British Postcodes - Inserting a Space

[bendoyle1983]

Hello,

I have a range of values which are extracted from our system, in this case postcodes.

British postcodes have to follow a certain format. There can be one of 5 permutations:

M1 1AA
M27 4LA
BL1 1AA
DN26 2WX
SW1A 1AA

As you can see, what we call the "postcode unit" (the last three characters) has to be separate from the postcode area and sector (the M1/M27/BL1/DN26/SW1A).

Unfortunately, our system extracts correctly inputted data (M1 1AA for example) with the everything erroneously joined together (so it pulls M11AA/M274LA and so on instead). Our system cannot be amended to extract the information correctly.

I need Excel to put a space in the right place on my "formatted data sheet" when I paste the data in on on "raw data" sheet.

I have tried =RIGHT, =LEFT and =MID, but due to the different lengths of postcodes (smallest is a 2/3, biggest is a 4/3 as per above) this hasn't really worked for me.

Alternatively, if there is a way for Excel to cut off the last three digits of a postcode, I can then use =CONCATENATE to rejoin them together with a space.



I hope this makes sense and someone can give me a few pointers! If you need any more info let me know...

Ta,

Ben

[sweep]

Hi,

This should correct your postcode

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

[stanleydgromjr]

bendoyle1983,

Can we see examples of the raw data, and what the correct result should be.

Pleaase post your workbook, or a sample without sensative information.

Click on the New Post button, then scroll down and see Manage Attachments.

[daddylonglegs]

You could use this formula

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

[bendoyle1983]

AMAZING, thanks so much guys, you've got me out of a rut!

I'm learning so much. The formulas here make sense when I'm shown!

THANKS!