Split unformatted addresses into separate columns

Hello Excel experts,

This is my first post here.

I need your help in solving an issue with separating out addresses into address, city, state and pin code.

This is how my data looks now.

503, LEVEL 5 , BLOCK A KELANA CENTRE POINT 3, JALAN SS 7/19, KELANA JAYA 47301 SELANGOR
1 ,JALAN PHASE INTAN, PHARSE NU3A1 NILAI UTAMA ENTERPRISE PARK NILAI 71800 NEGERI SEMBILAN
H-8-3 BAY AVENUE LORONG BAYAN INDAH 2 BAYAN LEPAS 11900 PULAU PINANG
2062 TAMAN DESA JAYA 2 CHENDERING 21080 TERENGGANU
5 USJ 6 JALAN USJ6/2J TAIPAN SUBANG JAYA 47610 SELANGOR


These are Malaysian addresses. The text after the pin code is the state and the text before it is the city.

I need to figure out a way to do so for 2000+ addressess. I tried using "RIGHT" and "FIND" functions and "Text to Columns" functions. All attempts were unsuccessful.

I would be very thankful if you could help me out here.

Thanks,
Palaniappan

Hello FDibbins,

Thanks for your quick reply.

You are right. There is no standard format at this atvall. I might have to try importing data in a different format.

If there is a way to separate out the 5-digit code and state, which follow the same format at the end, it would solve a good amount of my problem.

Thanks again.

Palaniappan

Thank you FDibbins.

This was a great step forward.

I was able to spread out most of the pincodes and I guess I can find states and cities with that using a database of pincodes, states and cities using vlookup or index match.

Thanks a ton!!