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

 

[Fluff]

Glad we could help & thanks for the feedback.