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

You may refer to my solution in this workbook.

• Selva says:

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,

Upload your workbook to OneDrive and share the link of the workbook here.

Sir,
We have tried this fornula but no resulation

=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)))

Regards – Pradip Das

• Hi,

What exactly are you trying to do? Explain the question and share some data.

• abhijit sarkar says:

this formula not working

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

• vallab says:

A big Thank you, Ashish. It worked!

• Bhavesh lad says:

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…

• Hi,

Upload the workbook to OneDrive/Google Docs and share the download link here. Also, is the length of the Pin code the same in all addresses?

• ann says:

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

• Hi,

Share the link from where I can download your workbook. Also, show the expected solution there.

• There is no file at that link. Share the link from where I can download your workbook.

• Hi,

I see the workbook now. I will need 2 additional columns – one which lists down all cities which can appear in your address list and another which will list down all States which can appear in your address list. Please ensure that the spellings of the Cities and States that you share should be the same as those in your address list.

• ann says:

ok sir,spellings is not an issue.

• So then create 2 tables with one column each. Share the download link.

• ann says:

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

• Hi,

In that workbook, please check the worksheet named Master List. I want a similar list for the addressed which appear in your dataset. If you cannot provide me that list, I cannot help you.

• Hi,

Remove extra spaces from the entries on sheet3. To extract cities, enter this formula in cell B1 of sheet1 and copy down

=LOOKUP(2,1/SEARCH(Sheet3!\$A\$2:\$A\$14,Sheet1!A1),Sheet3!\$A\$2:\$A\$14)

To extract States, enter this formula in cell C1 of sheet1 and copy down

=LOOKUP(2,1/SEARCH(Sheet3!\$B\$2:\$B\$12,Sheet1!A1),Sheet3!\$B\$2:\$B\$12)

Hope this helps.

• ann says:

sir,
i eliminated the space still error is coming up(#NAME?)

• Hi,

I do not know why that is happening. It worked fine for me. Upload the workbook in which you have applied my formula. I will review the formula there and correct the mistake.

• ann says:
• You have not implemented the formulas as I suggested the below. I just copied my formula shared earlier and pasted them in columns B and C. They are working absolutely fine.

• Gaurav says:

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.

• Sunil says:

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

• Sunil says:

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.

• Sunil says:

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

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

• Sunil says:

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

• Sunil says:

Hello sir…please update and help

• Sunil says:

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 ,- ,- , – , –

• Hi,

Try this formula in cell B2 and copy down

=LEFT(A2,SEARCH(“India”,A2)+4)

Hope this helps.

• Sunil says:

Thank you Sir 🙂

• Sanjay says:

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.

• rohit says:

Dear Sir,

how can can we separate address & city – state- pincode

• ROHIT says:

FORMAT – B4 Matrix Tower Sec – 132, Noida Opposite Jaypee Hospital, Noida, Uttar Pradesh, 201301

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

• alex says:

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

• Rakshit says:

Hi,
I dont have PINCode in my file but i need to add in my address column. How will i get in excel?

• Hi,

Your question is not clear. Share some data and show the expected result.