How to Seperate City and State from the address

[Ravishankar Thevar]

Hi All,

I have got at bulk data some of 1 lakhs address, how can i seperate city and state from that address.

Note :

Not all address are not in same length for example

Sr.No Address
1 Plot No. 113-114/9, Near Bus Stand , Pewa Chowk,Kaithal , Haryana, Pin-136027
2 SCO-327, Sector- 40D, Chandigarh , Pin-160036
3 Parag Plaza,1087 C,Puran Enclave,Main Market,Old Faridabad,Haryana-121002
4 Chhani Plaza, Radaur Road, Ladwa Distt. Kurukshetra Pin-136132
5 Kartar Enclave, Kaithal Road, Near Anaj Mandi, Cheeka Tehsil Guhla Distt. Kaithal Pin-136034
6 SCO-154, SECTOR-15, SONIPAT, HARYANA-131001
7 Opposite Tehsil and D.C.Office, Santoshgarh Road Una (H.P.) Pin-174303
8 Vill. & P.O. Binjalpur Dist. Ambala, Haryana-133206
9 Scf 293, Motor Market Manimajra Chandigarh-160101
10 Allahabad Bank Machchonda, Drm Office, Po-Kuldeep Nagar, Dist.-Ambala, Pin-133004

[shukla.ankur281190]

Hi Ravi Shankar,

Welcome to forum..

As i can see in you provided data there is not mention state name in each address Also can you highlight Dist and State in given example.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.