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.
Extract City, State and Pin code from an address string
{ 66 Comments }