Extract City, State and Pin code from an address string


For a lot of Excel users, a common problem is to extract City, State and Pin Code from an address string.  What compounds the issue is that there is no standardisation in an address string.  For e.g., one may end the address with a Pin code, while others may end it with a State and Country.  Some other variations could be:

1. End the address with Contact Numbers
2. There are no spaces between City, State and Pin code

There are of course many other variations which are possible and the sheer number of these variations makes it difficult to list down all of them.

Here is an example of address strings from where the State, City and Pin code need to extracted in three columns:

1. F-45, Pocket 1, Sector 198, Noida - 201303, Uttar Pradesh, India
2. S-45/B, Pocket 1-C, Phase II, Sector 189, Gurgaon, Haryana - 122002
3. RR-45/B, Pocket II-S, Cross 2, Noida - 201303, India
4. T-45, Sector 198, Lucknow - 226001, Uttar Pradesh, India
5. V-45(A), Sector 193C,Allahabad-211002 Uttar Pradesh India
6. V-45(A), Sector 193C,Allahabad-211002Uttar PradeshIndia

You may refer to my solution in this workbook.

RSS 47 Comments…

 Share your views
  1. tried the same formula to divide the address , but the formula is not working

    tried forumula


  2. Hi,

    Please ignore my previous mail. I have been able to solve the problem. Upload your actual data dataset (no matter how large) to Onedrive and share the download link with me.

  3. A big Thank you, Ashish. It worked!

  4. I want to extract a pincode from address list in a column..

    Pincode are randomly spread in a column in between or beginning or in end...in a cell

    I.have to do such formulation which can extract the pincode from 3k data which i have....pls assist me...

  5. sir,
    the formula is not working.i need to extract state and city name from the address block.

  6. ok sir,spellings is not an issue.

  7. sir i want to get city and state in the same table itself.just like your sample worksheet.

  8. How do I extract PIN code in following cases
    a. PIN code is 7 character due to a space in middle e.g. 400 001
    b. PIN code is 2 digit. Like Delhi-01


    • Hi,

      Please share more information. Will the cell containing the PIN code always have the PIN code at the end? Will there always be a - before the PIN code? Please share more sample data and show the expected result.

  9. Hi,
    Request your support in identifying a formula which will capture pin code with spaces and pin code not with spaces irrespective wherever they are in the address column.

    • Hi,

      Share some data and show the expected result.

      • Sir I have replied back on the mail received...please refer and guide

        • Hi,

          Which mail are you referring to? Please post your sample data and show the expected result.

          • Hi PFB, the demo data and formula which i used to extract Pin code
            formula used by me is only capturing the pincode, however, it fails to capture pincode with spaces.

            Formula - =INDEX(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),6),MATCH(TRUE,LEN(ABS(1*(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),6))))=6,0),1)

            Demo Data
            1) 122002, S-45/B, Pocket 1-C, Phase Ii, Sector 189, Karnal, Haryana - this can be extracted by the above formula

            Not able to extract the below Pincode with spaces:
            1 ) Shapoorji Complex, Block-C201, Room Number-102, New Town, Rajathat, Kolkata-700 135, West Bengal, India , Kolkata
            2) C/O Dilip Kumar Sharma, Town Thana Road, Hajipur, Vaishali - 8441 01, Bihar, India , Vaishali

            Please help

          • Hi,

            Try this array formula (Ctrl+Shift+Enter) in cell A2 and copy down

            =INDEX(MID(SUBSTITUTE(A2," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2," ","")))),6),MATCH(TRUE,LEN(ABS(1*(MID(SUBSTITUTE(A2," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2," ","")))),6))))=6,0),1)

            Hope this helps.

          • Hi sir,
            Provided array formula was really helpful and more importantly "IT'S WORK". Thank you very much...appreciate your prompt response in resolving the issue....

  10. Hello sir...please update and help

  11. Hello Sir,

    Again would like to help,

    Requirement : -
    Sir, I want to remove all the text and character after the word "India" which is mentioned in the address. Say address should end after the word "India".

    Demo Address data : -
    1) # 1018, 1St Main, 2Nd Cross , # 1018, 1St Main, 2Nd Cross, Manuvana ,East Post Office , Bangalore ,560040 , Karnataka , India And Bengaluru/ Karnataka
    2) # 180 C/O Krishna Reddy, 2Nd Main, 3Rd Cross, Near Srinivas Kalyanmantappa, Hongasandra. Begur Road, Bommanahalli, Bangalore- 560068, Karnataka, India , Bangalore
    3) 11-2-472/5/2, 2Nd Floor, Padma Arcade, Upparbsti, Namalgundu, Secunderabad 500061, Telangana, India , - ,- , - , - , - , -
    4) 12 Kennedy Street, Srinagar Colony, Tirumullaivayol, Kannan Theatre, Chennai 600062, Tamil Nadu, India , - ,- , - , - , - , -
    5) #30, New Sapna Store Building, Room No. 308, Royal Pg For Gents, 5Th Main Teachers Colony, 1St Block Koramangala, Opposite Mylapur Cafe, Hsr Layout, Bangalore-560034, Karnataka, India , - , Bangalore
    6) 134, Village Nandiali, Po Dharamgarh (Manauli), Tehsil And District Sas Nagar Mohali, Mohali-140306, Punjab, India , - ,- , - , Mohali ,- ,- , - , -

    Please help and request your support

  12. Thank you Sir 🙂

Leave a Comment

Your email address will not be published.