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