Finding city name from address and retuning to adjacent cell

[mountcleverest]

Hi!,

I'm completely new to the forum and dunno if this is a no-brainer coz it has got me in a fix. I have a list of addresses from which I have to find the city name. For reference, I have a list of Indian cities.

Is there a formula to do this so that the city name is returned to the adjacent cells to the respective addresses?

Many Thanks in advance!

Cheers!

Mukesh

---

 

[Vidar]

Hi

It shouldn't be to hard. But you have to show an example of your data tables, with the expected results

Vidar

 

[mountcleverest]

Hi

It shouldn't be to hard. But you have to show an example of your data tables, with the expected results

Vidar

Hi!


Addresses City Name

176, Mall Road, Near Sbi, Ambala-133001	Ambala
1-8-155, Near Paradise Circle, Beside Hdfc Bank, Pendergast Road, Secunderabad - 500003	Secunderabad
19/1, First Floor, Sait Colony, 1St Street, Egmore, Chennai - 600008	Chennai
41B, B Ganguli Street, 1St Floor, Central Plaza, Kolkata- 700047	Kolkata
5, Park Road, Thapar House, Infront Of Civil Hospital, Hazratganj, Lucknow - 226001	Lucknow
5/6, Aarsi Corner Building Nr. Gujarat College Ellis Bridge, Ahmedabad - 380009	Ahmedabad
65-B, Rajpur Road, Dehradoon - 248001	Dehradoon
9-8, Twin Towers, South Gandhi Maidan, Patna - 800001	Patna
B-172, Bhrigu Marg, Bani Park, Jaipur - 302016	Jaipur

<colgroup><col><col></colgroup><tbody>
</tbody>

The city names is an exhaustive list of Indian cities and is the reference for me.

Thanks!

 

[steve the fish]

If E1:E15 has your list of cities and A1 has your address this works (entered CNTL-SHIFT-ENTER):

In B1:

=IFERROR(PROPER(INDEX($E$1:$E$15,MAX(IF(ISBLANK($E$1:$E$15),-1,IF(ISERROR(SEARCH($E$1:$E$15,A1)),-1,1)*ROW($E$1:$E$15))))),"City Not Found")

 

[Vidar]

(deleted)

 

[mountcleverest]

If E1:E15 has your list of cities and A1 has your address this works (entered CNTL-SHIFT-ENTER):

In B1:

=IFERROR(PROPER(INDEX($E$1:$E$15,MAX(IF(ISBLANK($E$1:$E$15),-1,IF(ISERROR(SEARCH($E$1:$E$15,A1)),-1,1)*ROW($E$1:$E$15))))),"City Not Found")

Thanks a heap Steve! That really works. Forgive me please however, isn't there a shorter way to have this?

Cheers!

 

[Vidar]

Maybe this one?
=LOOKUP(9.99999999999999E+307,SEARCH("*"&$E$2:$E$10," *"&$A2&" "),$E$2:$E$10)

Adjust the ranges to your needs