Postcode Separater

**Jessie826** · 06-06-2012, 12:22 PM

Hello,

I have hundreds of addresses with their postcodes in one cell. I need to split or extract the postcode from the main address and put it into the cell adjacent.

E.G.

I have this in column A:
26, address 1, address 2, address 3, county, postcode

But I need all the postcodes to be in the cells next to their original address in column B.

All of the addresses are formatted differently; some have commas in between the county and postcode and some just have a space.

How can I sort these!?

I would really appreciate if anyone could help.

**Søren Larsen** · 06-06-2012, 12:31 PM

Could you post an example?

**tigeravatar** · 06-06-2012, 01:36 PM

This will extract the postcode regardless of comma, space, or comma and space. In cell B1 and copy down:
=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,",",REPT(" ",99))," ",REPT(" ",99)),99))

**Jessie826** · 06-07-2012, 04:39 AM

Thank you tigeravatar, I had to change the formula to:
=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,",",REPT(" ",99))," ",REPT(" ",89)),99))

This is brilliant, I am so grateful!