Split unformatted addresses into separate columns

[palaniappan0212]

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 <colgroup><col></colgroup><tbody> </tbody>

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

<tbody>
</tbody>

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

 

[bosco_yip]

1] Row 1, Heading >>
A1 : Addresses Data
B1 : City Address
C1 : Pin Code
D1 : State

2] A2 to A2000+, enter you addresses data

3] B2, formula copy down :

=SUBSTITUTE(A2," "&C2&" "&D2,)

4] C2, formula copy down :

=-LOOKUP(1,-RIGHT(LEFT(A2,LOOKUP(1,-MID(A2,ROW($1:$99),1),ROW($1:$99))),ROW($1:$99)))

5] D2, formula copy down :

=MID(A2,LOOKUP(1,-MID(A2,ROW($1:$99),1),ROW($1:$99))+2,250)

Regards

 

[pgc01]

Hi Palaniappan
Welcome to the board

Parsing addresses can be a difficult task.

You must analyse your addresses and derive clear rules of how to split them.

From what you posted, I understand that

1 - the last sequence of digits is the pin code
2 - the text after the pin code until the end of the address is the state

If this is correct, it will be easy to get them.

For the rest of the text, before the pin code, you'd need to post clear rules of how to get the city, etc.
For ex., in most countries cities names can have more than 1 word. If that's also the case for Malaysian cities, you'll have to tell us how excel can know where the city name starts.

 

[palaniappan0212]

@bosco_yip: You are just awesome !!!! I am mindblown right now. Pincodes and states are extracted out like a charm. Thanks a million. Thank you !!!!!

 

[palaniappan0212]

@pgc01.

Hi PGC,

You are right. The sequence is that the pin code is followed by the state name. bosco_yip's solution just did that.

Thank you for taking your precious time to analyse my problem.

Mr. Excel is awesome !!!

Thank you !