Extract City, State and Pin code from an address string

{ 66 Comments }

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.

Leave a Comment

Your email address will not be published.

*

  • tried the same formula to divide the address , but the formula is not working

    tried forumula

    =IF(RIGHT(TRIM(LEFT(A2,SEARCH(C2,A2)-1)),1)=”,”,LEFT(TRIM(LEFT(A2,SEARCH(C2,A2)-1)),LEN(TRIM(LEFT(A2,SEARCH(C2,A2)-1)))-1),TRIM(LEFT(A2,SEARCH(C2,A2)-1)))

  • 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.

  • 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…

  • 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

    Thanks
    Gaurav

    • 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.

  • 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.
    Thanks

          • 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….

  • 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

      • Hi can you help me with the formula to extract the pin code in the sample data listed
        11 – 45 – /13/10 Near Old Sivalayam Market, Viajayawada, Tavva Vari Street, Vijayawada Ho, Vijayawada – 520001, Opp Raja Chandra Dress
        Aligarh Ho, Aligarh – 202001, Near Sarai Hakeem
        950, Nirankari Street No 8, Basant Market, Millerganj, Ludhiana – 141003, Opp. Manju Cinema

        • Hi,

          Your pasted data is not clear. Is that all in a single cell or multiple cells? Share the link from where i can download your workbook and show the expected result very clearly.

          • Hi,

            Addresses can be very haphazard and do not follow any pattern. Separating an address field is therefore very difficult. You may try to use Data > Flash Fill.

  • Abhishek Building, G Block, Juhu Versova Link Road, Andheri West, Mumbai – 400053, Opposite Hdfc Bank
    how to extract pincode

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

      • Hi 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 and mobile number

        Formula— =MID(E6,FIND(“@@@@@@”,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E6,1,”@”),2,”@”),3,”@”),4,”@”),5,”@”),6,”@”),7,”@”),8,”@”),9,”@”),0,”@”)),6)*1

        Demo Data

        1) PIN……676305 ABU TALHA , GRAM TOEAN [OP] TOWN SARAI MEER SARAIMEER AZAMGARH PHULPUR ……, AZAMGARH , Uttar Pradesh, India, 9721994650, 9936718425, India

        this can be extracted by the above formula not able to extract the below pincode with spaces mobile number

        2) PRAVEEN KUMAR K, S/O B BALAN NAIR KARUVINCHAYIAM HOUSE KUTTIKOL PO .CHENGALA [VIA] PIN 671 541, KASARAGOD, Kerala, India, 8136920654, 9539813011, India

        3) PRAVEEN KUMAR K, S/O B BALAN NAIR KARUVINCHAYIAM HOUSE KUTTIKOL PO .CHENGALA [VIA] PIN 6715 41, KASARAGOD, Kerala, India, 8136920654, 9539813011, India

        4) BALKEESHPEGAM NCHIRAHAMMAD, C/O NASEERAHAMMAD , NO – 2/477 , THATTAN KULAM STREET , UDAYAMARTHANDAPURAM , THIRUVARUR, Tamil Nadu, India, 9688353770, 6385508410, India

        Please help

        • Hi,

          Assuming the addresses are in range A2:A4, enter this formula in cell B2 and drag down

          =IFERROR(LET(txtsp,TEXTSPLIT(SUBSTITUTE(A2,” “,””)&”X”,TEXTSPLIT(SUBSTITUTE(A2,” “,””)&”X”,SEQUENCE(10,,0),,1),,1),FILTER(txtsp,LEN(txtsp)=6)),””)

          Enter this formula in cell C2 and drag down. Ensure that columns D:H are empty

          =LET(txtsp,TEXTSPLIT(A2&”X”,TEXTSPLIT(A2&”X”,SEQUENCE(10,,0),,1),,1),FILTER(txtsp,LEN(txtsp)=10,””))

          These formulas will work in Microsoft 365.

          Hope this helps.