Identify and extract postcode from address

[dianeblackfamily]

I wonder can anyone help?

Column D contains a complete address eg
60 Braugham Road, Wallasey, Merseyside CH46 1LP

I would like to be able to extract the postcode into column E, and then the address into columns F, G, H etc so that I can use the data for mailmerge docs. Is there a forumula or a macro that can be used to identify and extract the postcode? The postcode will usually be either one or two letters with one or two numbers, then a number and two letters. eg it might be as above or B6 9XF etc.

Any suggestions gratefully received!

 

[Rick Rothstein]

In your first message, you said...

"The postcode will usually be either one or two letters with one or two numbers,
then a number and two letters. eg it might be as above or B6 9XF etc"

I took this to mean that the first part of the post code would end in a number and the second part would begin with a number... obviously (now) that is not the case. Back to the drawing board. About that DX number... can it ever appear at the end of the text without being followed by any other text? Also about this example...

"care of Edwards Duthie Solicitors (Reference: Hilary Green) DX200850, Ilford 4"

where the DX code is followed by a town and number... is that number always only a number? Or could the number (4) be followed by another character, specifically, a non-digit (like "Ilford 4A" for example)?

 

[dianeblackfamily]

Hi Rick,
So sorry about the postcode mess-up. I hadn't realised that the London postcodes follow a rule of their own!
To answer your questions - the DX number will sometimes not have any text following it (although it should). There are no examples like that in the data I am testing this time however.
When the DX number is followed by a town and number, there is never another letter to follow, it is just a number.
Sorry it has got so complicated - there's a lot to be said for the standardisation of postcodes!

 

[Rick Rothstein]

To answer your questions - the DX number will sometimes not have any text following it (although it should). There are no examples like that in the data I am testing this time however.
When the DX number is followed by a town and number, there is never another letter to follow, it is just a number.

Okay, let's take another stab at this. Put this formula in cell F2 and copy it down...

=IF(ISNUMBER(-LEFT(TRIM(RIGHT(SUBSTITUTE(D2," ",REPT(" ",999)),999))))*NOT(ISNUMBER(-RIGHT(TRIM(RIGHT(SUBSTITUTE(D2," ",REPT(" ",999)),999))))),TRIM(RIGHT(SUBSTITUTE(" "&D2," ",REPT(" ",999)),1998)),"")

Hopefully, this one worked.

 

[dianeblackfamily]

Rick - you are a star!!! That has worked perfectly. Well done indeed and many, many thanks

 

[Rick Rothstein]

That has worked perfectly. Well done indeed and many, many thanks

Great news! You are quite welcome, of course.

 

[A[L]C]

I am very sorry to bring up an old thread, however the formula that Rick has posted is working excellently. Is there however a way that I can reverse it to show only the text which is not the post code?

Many thanks
Paul

 

[Rick Rothstein]

I am very sorry to bring up an old thread, however the formula that Rick has posted is working excellently. Is there however a way that I can reverse it to show only the text which is not the post code?

Just so there is no confusion as to what you want, can you post some representative examples of the text you might have and then show us the text you want the formula to output for you?

 

[A[L]C]

Just so there is no confusion as to what you want, can you post some representative examples of the text you might have and then show us the text you want the formula to output for you?

Hi Rick,

Thanks for replying. Here is what I mean. Although my original text is not quite the same as the OP, your formula works well. I need to split the original text in to two columns as shown below.

Original Text			Rick's formula			Desired output
HIGH WYCOMBE HP13 6QT		HP13 6QT			HIGH WYCOMBE 
BERKSHIRE SL4 5JL		SL4 5JL				BERKSHIRE 
BERKSHIRE SL1 2SF		SL1 2SF				BERKSHIRE 
WOODLEY RG5 4LY			RG5 4LY				WOODLEY 
SHEFFIELD S6 6RW		S6 6RW				SHEFFIELD 
BUCKINGHAMSHIRE SL7 1YA		SL7 1YA				BUCKINGHAMSHIRE 
READING RG5 4JB			RG5 4JB				READING

I hope this makes sense.

Many thanks
Paul

 

[ukmikeb]

Hi

Try :-

For the Post Code
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1),99))

For the Town/County
=TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",99),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1),99))

hth

 

[A[L]C]

Hi

Try :-

For the Post Code
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1),99))

For the Town/County
=TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",99),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1),99))

hth

that seems to work, thanks a lot.

Could you possibly explain how it works please?