Inserting space 3 characters from right

[jennybenny]

Hi,

I have what I think is quite a simple issue, but it's baffled me! I have a list of postcodes and I need to add a space in the middle in order to use it with another data source which are already formatted this way.

I currently have postcodes such as 'PR88AG' and I need to add a space before the last 3 characters. From what I've already attempted/ found, the formula sometimes doesn't work as the left half of the postcode can vary from 2-4 characters- i.e. P8 4AG/ PR10/ PR8. From my knowledge the second half of UK postcodes always have 3 characters, so I need to add the space from the right, not from the left if that's possible please.

Thanks in advance.

 

[Oeldere]

maybe like this

=left(a1,3)&" "&right(a1,3)

 

[danzon]

how about

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


this will isolate the last 3 no matter how many digits are in your postal code

 

[Jonmo1]

Another...

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

 

[jennybenny]

Thanks, this works great.

Originally it didn't seem to be working on the raw data set I have, but was working if I typed the postcode in manually- it finally dawned on me that there is a space at the end of each cells in the data I have been sent (which is why I couldn't get it to work originally when I tried to write it). So I have used substitute to remove the end space and then the formula by danzon to get the space 3 from right.

Thanks so much for your help!