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!

 

[dianeblackfamily]

Joemo,
It has run through now, sorry not sure what happened. Problem entries remain as Trade Park already mentioned above, and also DX addresses such as [TABLE="width: 460"]
<tbody>[TR]
[TD="width: 460"]DX96000, High Wycombe 8. Mostly it is spot on, though. Than k you very much indeed for your help.[/TD]
[/TR]
</tbody>[/TABLE]

 

[dianeblackfamily]

Thanks Rick, This one works a treat, except for on the DX addresses (DX is an alternative postal system in case you are not familiar - the DX address does not contain a postcode)This is an example of how what it extracted from the address into columns D and F:[TABLE="width: 1032"]
<tbody>[TR]
[TD="width: 519"]DX96000, High Wycombe 8[/TD]
[TD="class: xl67, width: 405"]DX96000, High[/TD]
[TD="class: xl67, width: 108"]Wycombe 8
[/TD]
[/TR]
</tbody>[/TABLE]

Is there any refinement that can be done at all? If not, it is still really good. thank you so much

 

[Legacy 221039]

ignore this...I see problem is already solved - sorry, can't seem to delete my reply entirely M

[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[/TR]
</thead><tbody>[TR]
[TH]

[/TH]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Thanks Rick, This one works a treat, except for on the DX addresses (DX is an alternative postal system in case you are not familiar - the DX address does not contain a postcode)This is an example of how what it extracted from the address into columns D and F:[TABLE="width: 1032"]
<TBODY>[TR]
[TD="width: 519"]DX96000, High Wycombe 8
[/TD]
[TD="class: xl67, width: 405"]DX96000, High
[/TD]
[TD="class: xl67, width: 108"]Wycombe 8
[/TD]
[/TR]
</TBODY>[/TABLE]

Is there any refinement that can be done at all? If not, it is still really good. thank you so much

I am sure the formula can be refined, but as you correctly surmised, I am not familiar with British(?) post codes, so I did not know about DX alternatives. Can you tell me the rule concerning them? What should go where when the DX is followed by one word, two words, three words, etc.? The more information you give about what to do with DX codes under varying conditions, the better able we will be to write a formula or code around them.

 

[JoeMo]

Joemo,
It has run through now, sorry not sure what happened. Problem entries remain as Trade Park already mentioned above, and also DX addresses such as [TABLE="width: 460"]
<tbody>[TR]
[TD="width: 460"]DX96000, High Wycombe 8. Mostly it is spot on, though. Than k you very much indeed for your help.[/TD]
[/TR]
</tbody>[/TABLE]

Here's a revision that works for the Unit 9 and the DX problems too.

Sub ExtractPostCode()
Dim lR As Long, R As Range, vA As Variant, rStr As String
lR = Range("D" & Rows.Count).End(xlUp).Row
Set R = Range("D2", "D" & lR)
For Each c In R
    vA = Split(c.Value, " ")
    For i = LBound(vA) To UBound(vA)
        If vA(i) Like "[A-Z]*#*" And InStr(vA(i), ",") = 0 Then
        c.Offset(0, 1).Value = vA(i) & " " & vA(i + 1)
        rStr = " " & c.Offset(0, 1).Value
        c.Replace rStr, ""
        Exit For
        End If
    Next i
Next c
End Sub

 

[dianeblackfamily]

Hi Rick,
Thanks for your help. The DX is an alternative postal service to the royal mail. The DX number is a bit like a postbox number and is the letters DX, followed by 5 numbers, then it usually has a Town name and ref as in the above example: High Wycombe 8.
I just need to ignore these type of entries and treat them as if they do not have a post code. ie they do not need anything extracting from them. Hope this makes sense?

 

[Rick Rothstein]

Hi Rick,
Thanks for your help. The DX is an alternative postal service to the royal mail. The DX number is a bit like a postbox number and is the letters DX, followed by 5 numbers, then it usually has a Town name and ref as in the above example: High Wycombe 8.
I just need to ignore these type of entries and treat them as if they do not have a post code. ie they do not need anything extracting from them. Hope this makes sense?

Okay, change the formula in F2 to this...

=IF(AND(LEFT(D2,2)="DX",ISNUMBER(-MID(D2,3,5))),"",IF(ISNUMBER(-LEFT(TRIM(RIGHT(SUBSTITUTE(" "&D2," ",REPT(" ",999)),999)))),TRIM(RIGHT(SUBSTITUTE(" "&D2," ",REPT(" ",999)),1998)),""))

 

[dianeblackfamily]

Thanks. This works really well, except for instances such as this where it has taken Ilford 4 as the postcode:

[TABLE="width: 993"]
<tbody>[TR]
[TD="width: 490"]care of Edwards Duthie Solicitors (Reference: Hilary Green) DX200850, Ilford 4[/TD]
[TD="width: 439"]care of Edwards Duthie Solicitors (Reference: Hilary Green) DX200850,[/TD]
[TD="width: 64"]Ilford 4[/TD]
[/TR]
</tbody>[/TABLE]

Any ideas? No worries if you don't it is almost perfect and will save me lots of time! thank you very much indeed

 

[Rick Rothstein]

Thanks. This works really well, except for instances such as this where it has taken Ilford 4 as the postcode:

[TABLE="width: 993"]
<tbody>[TR]
[TD="width: 490"]care of Edwards Duthie Solicitors (Reference: Hilary Green) DX200850, Ilford 4
[/TD]
[TD="width: 439"]care of Edwards Duthie Solicitors (Reference: Hilary Green) DX200850,
[/TD]
[TD="width: 64"]Ilford 4
[/TD]
[/TR]
</tbody>[/TABLE]

Any ideas? No worries if you don't it is almost perfect and will save me lots of time! thank you very much indeed

I mistakenly assumed from your previous message that the DX code had to come first in the address (remember, I do not know anything about post codes or their rules). Anyway, I have reworked the formula for cell F2 and I think this should work (I hope, I hope) for all your situations...

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

 

[dianeblackfamily]

Hi Rick, You are very clever - that has got rid of that problem. However... there are now some addresses that worked with the last formula, but now do not work. The postcode does not not appear in column F. These type of addresses are all London postcodes which do not fit the letter/number sequence of the regional postcodes - they have an extra letter after the first number as follows:

[TABLE="width: 816"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]61 Conduit Street, London W1S 2GB[/TD]
[TD]61 Conduit Street, London[/TD]
[/TR]
[TR]
[TD]1-3 Strand, London WC2N 5EH[/TD]
[TD]1-3 Strand, London[/TD]
[/TR]
[TR]
[TD]Great Minster House, 76 Marsham Street, London SW1P 4DR[/TD]
[TD]Great Minster House, 76 Marsham Street, London[/TD]
[/TR]
</tbody>[/TABLE]