If anyone is stuck with anything Excel or Access related then send me a note and I will try and help, I love Excel and am always happy to try and discover new skills.
On this note I was asked a little while ago how to split a postcode in Excel, as it was put to me:
"How do you split the first part of the postcode out when it can sometimes be 4 digits and sometimes 3 digits."
Now there is a way to do this using isnumeric - but I found a much simpler way is to look at the part of the postcode you know doesn't change which is the last three digits, the last three digits are always three so therefore you know that the space will always come from the fourth space from the right.
=LEFT(A1,LEN(A1)-3)&" "&RIGHT(A1,3)
Here what I am doing is trimming off all but three digits from the left and then concatenating the three right most after adding the space so
RH11RH, RH111RH and R111RH will become RH1 1RH, RH11 1RH and R11 1RH respectively.
If you want to do the opposite then you can use this formula
=REPLACE(A1,FIND(" ",A1),1,"") to take the space out of a postcode.
Other useful Postcode formula include
=LEFT(G2,FIND(" ",G2)-1) to find the outcode of a postcode that contains a Space or simply
=LEFT(A1,LEN(A1)-3) to find the out code of a Postcode without a space.
No comments:
Post a Comment