Combining formulae for UK postcodes

[paulsolar]

Hi All

I have a method of sorting out typos and in correct entries of UK postcodes, but it takes 4 helper columns to do it.

Below are the formulae I use to do it, the original postcode is in A2,
In B2 I have =TRIM(A2) this is to get rid of any superfluous spaces at the beginning or end of the postcode
In C2 I have =UPPER(B2) this sort out the case as all the letters need to be upper case
In D2 I have =SUBSTITUTE(C2," ","") this removes any spaces in the wrong place
In E2 I have =IF(LEN(D2)-3=2,LEFT(D2,2)&" "&RIGHT(D2,3),IF(LEN(D2)-3=3,LEFT(D2,3)&" "&RIGHT(D2,3),IF(LEN(D2)-3=4,LEFT(D2,4)&" "&RIGHT(D2,3)))) this puts the space in the correct place.

I have tried combing the formulae together but cant seem to get it to work, are any of you formulae experts able to assist me please so that I just need one formula in B2

Cheers

Paul

 

[Cubist]

Can you post some data sample?

 

[paulsolar]

Hi, many thanks for your prompt reply.

Postcodes in the UK always end with NLL proceeded by a space (where N is a number and L is a letter)

The first part of the postcode can vary in length (hence the if statements) and is a combination of Letters and Numbers with quite a few variations.

Here are some examples of correctly formatted post codes, NP10 9AF, W1A 4TT, B12 5QR, L1 9PP

Here are some examples of the postcodes that I receive.. " np1 09aF" , "w1 A4tt ", B1 25qr", "L19pp "

I am not aware of any postcodes where the left part is greater than 4 letters or numbers, but just in case I should probably if added an additional if statement to cover the left part being 5.

I hope this is clear for you

Cheers

Paul

 

[Cubist]

Thanks for the examples. Try this:

=UPPER(SUBSTITUTE(TEXTBEFORE(B4,RIGHT(TRIM(B4),3))," ","") & " " & RIGHT(TRIM(B4),3))

 

[Georgiboy]

Simply combining the logic of your original formula might look like:

=LET(pc,SUBSTITUTE(UPPER(TRIM(A2))," ",""),IF(LEN(pc)-3=2,LEFT(pc,2)&" "&RIGHT(pc,3),IF(LEN(pc)-3=3,LEFT(pc,3)&" "&RIGHT(pc,3),IF(LEN(pc)-3=4,LEFT(pc,4)&" "&RIGHT(pc,3)))))

 

[paulsolar]

Thanks Guys thats brilliant

Georgiboy, I would never have considered doing it you way but to be honest it's very clever. I've never used LET, but i will certainly be looking into it!!

Thanks for you help, really appreciate it

Cheers

Paul

 

[Cubist]

I don't know if you tried my formula but I had my reference on B4, you might need to adjust it to A2.

 

[Fluff]

As all UK postcodes have 3 characters after the space, you could use

=LET(pc,SUBSTITUTE(UPPER(TRIM(A2))," ",""),REPLACE(pc,LEN(pc)-2,0," "))

 

[paulsolar]

I don't know if you tried my formula but I had my reference on B4, you might need to adjust it to A2.

Hi, i spotted this and altered it and it worked fine

 

[paulsolar]

As all UK postcodes have 3 characters after the space, you could use

=LET(pc,SUBSTITUTE(UPPER(TRIM(A2))," ",""),REPLACE(pc,LEN(pc)-2,0," "))

Hi Fluff, I'll check yours out as well