Finding city name from address and retuning to adjacent cell

mountcleverest

New Member
Joined
Oct 10, 2014
Messages
3
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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Vidar

Well-known Member
Joined
Jul 19, 2012
Messages
1,254
Hi

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

Vidar
 

mountcleverest

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

<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

Well-known Member
Joined
Oct 20, 2009
Messages
8,112
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 19, 2012
Messages
1,254

ADVERTISEMENT

(deleted)
 

mountcleverest

New Member
Joined
Oct 10, 2014
Messages
3
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

Well-known Member
Joined
Jul 19, 2012
Messages
1,254
Maybe this one?
=LOOKUP(9.99999999999999E+307,SEARCH("*"&$E$2:$E$10," *"&$A2&" "),$E$2:$E$10)

Adjust the ranges to your needs
 

Watch MrExcel Video

Forum statistics

Threads
1,109,518
Messages
5,529,312
Members
409,862
Latest member
lbisacca
Top