Add a space into UK postcodes

[hayleym]

Hi everyone,

I am currently working on a list that contains 9k UK addresses. The postcodes are in the following format (no space):

B11LT
&
B120LD

As you can see that there is no set pattern with the amount of characters.

A postcode will always ends with 3 characters (number, letter, letter). Using this info, is there a way I can add the space in to all 9k postcodes?

All responses will be greatly appreciated
Hayley

 

[Special-K99]

Try

=LEFT(A1,LEN(A1)-3)&" "&RIGHT(A1,3)

 

[Peter_SSs]

Welcome to the MrExcel board!

Also ..

=REPLACE(A1,LEN(A1)-2,0," ")

 

[hayleym]

Hi Guys,

Both work perfectly!!

Thank you so much for your prompt replies, very much appreciated!!

Hayley

 

[Peter_SSs]

Hi Guys,

Both work perfectly!!

Thank you so much for your prompt replies, very much appreciated!!

Hayley

Glad they worked for you. Thanks for letting us know.

 

[dsharp7th]

Try

=LEFT(A1,LEN(A1)-3)&" "&RIGHT(A1,3)

Welcome to the MrExcel board!

Also ..

=REPLACE(A1,LEN(A1)-2,0," ")

a lot has happened in the years since Special-K99 and Peter_SSs posted these solutions, did they know that eight years in the future, someone facing an all night session of manually editing an endless list of postcodes would have hours saved and the gift of sleep, as a result of their helpful posts. thanks so much!

 

[Peter_SSs]

@dsharp7th
Welcome to the MrExcel board!
Glad that this was still help to somebody. Thanks for letting us know.