Address/Postcode seperate

[tt2k]

Hello,

I have a problem where address data on a sql database is quite messy. When copying to excel, it looks as per the sample attachment. Is there a formula to clean this up or to just even seperate the postcode only from the address column?

As you can see, some of the addresses are seperated by comma, normal spacing, double spacing etc. I have about 5000 rows to clean this up.

Any help would be much appreciated.

Thank you

[cytop]

What you don't mention is if the postcodes are formatted correctly - Outbound code, Space, Inbound code. They are in your sample so am assuming the sample is correct...

=TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),2*99)) in B2 and copied down.


EDIT: No, they're not. M212? Ignore this

[tt2k]

M212 is a wrong UK postcode format which means it wouldn't work for that one. Those one will probably have to be done manually after the formula so your formula works great.