+ Reply to Thread
Results 1 to 4 of 4

TO find the city name in the address string

  1. #1
    Forum Contributor
    Join Date
    09-15-2011
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    135

    TO find the city name in the address string

    Hi All,

    Good day,

    Would you please clarify my doubt i have a set of database list contains city info as well. my query is which formula will help me to extract the city name alone in the nearest cell. i have below mentioned the data. kindly have a look and help me to resolve the issue.

    S No Address
    1 419 MTH ROAD, PATTABHIRAM MADRAS
    2 27 A,1 ST FLR,TRUNK ROAD, POONAMALLEE CHENNAI
    3 CENTRE POINT, NO. 221 AND 222, LLOYDS ROAD, GOPALAPURAM CHENNAI TAMILNADU 600086
    4 27 NEW MGR, MAIN ROAD, KANDANCHAVADI, PERUNGUDI MADURAI TAMILNADU 600096
    5 50, ARYA GOWDA ROAD, WEST MAMBALAM BENGALORE TAMILNADU 600033
    6 287, T.H ROAD, OLD WASHERMENPET, MUMBAI 600021


    The result would be like

    1 MADRAS
    2 CHENNAI
    3 CHENNAI
    4 MADURAI
    5 BENGALORE
    6 MUMBAI

    Thank you in advance, Please reach out to me if you have any doubts on this.

    Regards
    Rajuganapathy

  2. #2
    Registered User
    Join Date
    03-06-2012
    Location
    sydney, australia
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: TO find the city name in the address string

    Hi Raju,

    You can perform a lookup and extract the data from matching existing cities from an external with your database extract the information.

    Noticed.
    1. Madras city is renamed Chennai.no longer exists.
    5. Benagalore is not in tamilnadu.
    6. Mumbai postcode does not begin with 6XXXXXX and also washermenpet is in chennai.

    Is your database correct or the data you listed is something you just made up.

    Post your database as an attachment, i can assist.

    Krishna

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: TO find the city name in the address string

    The only way to solve this problem is to download the list of cities (can be found on Wikipedia)
    https://en.wikipedia.org/wiki/List_o..._by_population
    What I did is I simply copies for from Wikipedia and it took me about 10-15 minutes. (some cells needed to be cleaned up)

    Use this formula in cell B2 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If you still using Excel 2003 use this one
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Please attached Excel File
    Attached Files Attached Files
    Last edited by AlKey; 06-23-2018 at 09:15 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Forum Contributor
    Join Date
    09-15-2011
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    135

    Re: TO find the city name in the address string

    Hi Alkey,

    Thank you very much for your kind response . The issue was solved.

    Regrds
    Rajuganapathy

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pulling company name, street address, city, state, and zip from address
    By UNREAL2K4 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-13-2015, 12:53 PM
  2. Address and city splitting
    By sathishkm in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-13-2014, 12:11 PM
  3. Converting a complete, single column address into separate columns for ADDRESS, CITY, ST,
    By jeffrogerssn in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-08-2012, 10:34 AM
  4. How to get rid of the city and state in the address?
    By excel1212 in forum Excel General
    Replies: 2
    Last Post: 09-08-2012, 05:30 PM
  5. Excel 2007 : Extract CITY in ADDRESS string
    By kwj1966 in forum Excel General
    Replies: 4
    Last Post: 02-03-2012, 04:27 PM
  6. Extract City from Address
    By Little Master in forum Excel General
    Replies: 5
    Last Post: 09-07-2011, 11:45 AM
  7. Replies: 2
    Last Post: 03-03-2005, 09:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1