How do I split UK postcodes that don't contain a space using Excel?

Question

I have a list of customers' addresses that were sent in by a client but their postcodes don't contain a space. e.g. SP177FG.
I need to make a formula that splits the postcode at a certain point based on how many characters the postcode contains. e.g. if postcode length is 7, insert a space after character 4. if postcode length is 6, insert space after character 3 etc.

Is anyone able to help?

Answer 1

This is easy to do, because the "inward code" (the last element) is always 1 digit and 2 characters.



So this formula should always work:-



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



The only problem could be with trailing blanks. In which case you would have to include TRIM.

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

Answer 2

The first two letters are the town (usually the biggest) eg PR is Preston L is Liverpool M is Manchester (so it can be one or two letters). The next number, or two numbers is the area eg 1 is usually the city centre, 33 would be out in the countryside somewhere! Then the next number is the smaller area eg 6 would cover say half a village, then the last two letters are a smaller area (eg an estate or few roads) then the last number is a few houses say up to six or seven. so you can get within a few houses but not the exact number.

Answer 3

It is difficult because of the variety of formats of UK postcode.



The most common format is: AB12 3EF (2 letters, 2 numbers, space, 3 letters.)

But there are others: W1 ABC (a central London type code, using the compass, N,S,E,W and C)

Another type of London code: W2H 3BC (with a H before the space)



Generally, the space comes before the last 3 characters, and they usually end with 1 digit and 2 letters.

Answer 4

Hi,

As already said, the last three characters keep the same format so if you put the postcodes you were given in column A and the formula below in column B it will produce the correct format including the space.



=REPLACE(A1,LEN(A1)-2,3,CONCATENATE(" ",RIGHT(TRIM(SUBSTITUTE(A1,CHAR(160),CHAR(32))),3)))



Bill

Answer 5

A1 is your postcode, B1=MID(A1,1,LEN(A1)-3)&" "&RIGHT(A1,3)



Sorry Andrew, didn't read your answer properly. Good solution., mine is almost as good (I think).