Extract City from Address

**Little Master** · 06-19-2010, 07:58 AM

Hi All,

I have a list of addresses from which I need to extract city for that location from another list of cities to cell corresponding to address. I tried using vlookup but it didn't help. Can anyone of you look into file & suggest me proper solution.

Thanks in Advance.

**martindwilson** · 06-19-2010, 08:23 AM

see attached i prepared an unique list of cities in column a having duplicates is just clutter
then used =LOOKUP(2^15,SEARCH($A$2:$A$138,E2),$A$2:$A$138) in column g dragged down

**Little Master** · 06-19-2010, 08:31 AM

Thanx a billion martin... it worked for me... thnx again....

**Little Master** · 06-25-2010, 07:14 PM

The formula is working absolutely fine with me, I have even understood the formula. But the only thing I am curious about is "2^15" used in formula. Can someone pls put some light on it & tell me y this was used & what does this mean. Thanks in advance

**martindwilson** · 06-25-2010, 07:31 PM

10^308 or 9.999999999E+307 is the biggest number you can use
2^15=32768
its just that you must use a bigger number than you are likely to be searching for so lookup returns

If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value.

**terrysoper1973** · 09-07-2011, 11:45 AM

I tried the formula, but run into a little trouble. Say a record like this:

255 SIMCOE ST N APT 505 OSHAWA L1G 4T4, ONTARIO

Oshawa is the tarket, but both Oshawa and Simcoe appear in the lookup list. Because Simco comes last in the a-z, it gets picked.

Is there a way to search from the left side a certian number of spaces?

Thanks
Terry