problem lookup function
 

I have 250 names of towns in work sheet " DU " starting at cell D26 down to
D250.
those names are like this : Dubbo (g7)
Darwin (a2)
Sydney (k5) and so on .
In work sheet " Towns " column " B " I have the same names without the last
4 caracters, (g7) , (a2) , (k5) like this Dubbo
Darwin
Sydney
In column " C " opposite the towns names I have a distance number 505

220

710
In column cell " A45 " I have the formula
=LEFT(DU!D26,LEN(DU!D26)-RIGHT(4))
which gives me the name of the town wihout the last 4 caracters Dubbo

Darwin

Sydney
In column ' L " I have a lookup function: =VLOOKUP(A45,B2:C250,2,FALSE)
which should give me :- 505 , but it returns #N/A

Can any one help me please

thank you for your time
regards B Gras

Your problem is in the formula in cell A45. You are capturing the space
" " after the town name and before the "(g7)". The easy fix is to
change the formula in A45 to remove the last 5 characters instead of
four =LEFT(DU!D26,LEN(DU!D26)-RIGHT(*5*)).

The issue you will then face is if the coordinate or whatever the
"(g7)" represents ever changes in length. If it is possible that you
could have a (g12) or an (AA5) then you will again be faced with having
the space as part of the town name. A way to avoid this is to find the
space in the text string and only take the town name before the space.
=LEFT(DU!D26,LEN(DU!D26)-(LEN(DU!D26)-(FIND(" ",DU!D26,1)-1)))

Then your problem will be if you have a town name with a space as part
of the town name such as New York. Your safest bet is to locate the
open parenthesis "(" and to remove text that is to the the right of one
position left of the open parenthesis
=LEFT(DU!D26,LEN(DU!D26)-(LEN(DU!D26)-(FIND("(",DU!D26,1)-2)))


Hope this helps,
Adam


--
AdamD
------------------------------------------------------------------------
AdamD's Profile: http://www.excelforum.com/member.php...o&userid=24530
View this thread: http://www.excelforum.com/showthread...hreadid=381229

Change your text extraction formula!
You're not removing the *space* after the name.

Try either of these:

=LEFT(DU!D26,LEN(DU!D26)-5)

=LEFT(DU!D26,FIND(" ",DU!D26)-1)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"bill gras" wrote in message
...
I have 250 names of towns in work sheet " DU " starting at cell D26 down to
D250.
those names are like this : Dubbo (g7)
Darwin (a2)
Sydney (k5) and so on .
In work sheet " Towns " column " B " I have the same names without the last
4 caracters, (g7) , (a2) , (k5) like this Dubbo
Darwin
Sydney
In column " C " opposite the towns names I have a distance number 505

220

710
In column cell " A45 " I have the formula
=LEFT(DU!D26,LEN(DU!D26)-RIGHT(4))
which gives me the name of the town wihout the last 4 caracters Dubbo

Darwin

Sydney
In column ' L " I have a lookup function: =VLOOKUP(A45,B2:C250,2,FALSE)
which should give me :- 505 , but it returns #N/A

Can any one help me please

thank you for your time
regards B Gras