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!

 

[mgm05267]

Hi Dianeblackfamily,

One way is to use "Text to Columns" option.

Other way is .. if your Zip code text is constant, like 8 characters including the space between CH46 1LP, then you can use the formula =RIGHT(D1,8)

 

[dianeblackfamily]

Thanks for these suggestions MGM. However, I dont think I can use text to columns to take off the postcode as there is not a comma or a full stop before the postcode - only a space. You might know a way to use text-to-columns for this?
The formula you suggest wouldn't work on each line as the postcode may be between 6 and 8 characters long and not all entries have a postcode (sorry, I should have mentioned this)

 

[Vidar]

Hi

There must be an easier solution, but in the mean time this works for the example given.
In column E:
=MID(D1,FIND("zzz",SUBSTITUTE(D1," ","zzz",SUMPRODUCT(1*((MID(D1,ROW(INDIRECT("1:"&LEN(D1))),1))=" "))-1))+1,LEN(D1))

In column F:
=LEFT(D1,FIND(",",D1,1)-1)

In column G:
=MID(D1,LEN(F1)+3,FIND(",",D1,LEN(F1)+2)-LEN(F1)-3)

In column H:
=MID(D1,LEN(F1&G1)+4,FIND(" ",D1,LEN(F1&G1)+5)-LEN(E1&F1&G1)+4)

Vidar

 

[Robert Mika]

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!

In E1 to get the post code:
=TRIM(RIGHT(SUBSTITUTE(TRIM(D1)," ",REPT(" ",99)),2*99))

but can you post more examples?

 

[JoeMo]

Thanks for these suggestions MGM. However, I dont think I can use text to columns to take off the postcode as there is not a comma or a full stop before the postcode - only a space. You might know a way to use text-to-columns for this?
The formula you suggest wouldn't work on each line as the postcode may be between 6 and 8 characters long and not all entries have a postcode (sorry, I should have mentioned this)

This code will extract the post code to column E and leave the balance of the address (without the post code) in column D for you to further decompose. Assumes your data begins in D2 - change code to suit:

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]*#*" 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 Vidar,
thanks for these formulas. Limited success however - when there is no postcode included in the address, the formula extracts part of the address instead. I can split the address Ok using text to columns once I have taken off the postcode, but it is just identifying and extracting the postcode that I am struggling with (although it would be nice to do it all with formulas, as you suggest)
Any more ideas?
These are they type of addresses that it didn't work on:
[TABLE="width: 453"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Stag Gates House, 63/64 The Avenue, Southampton[/TD]
[/TR]
[TR]
[TD]Matrix House, Mountview, Eastleigh, Hants

Thanks so much[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Give this a try. Put these formulas in the indicated cells and copy down...

E2: =TRIM(SUBSTITUTE(D2,F2,""))

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

 

[Ferdi24]

These solutions all seem quite complicated...
Is this a one-off task?

If so, I would
- Do text to columns with "," as the delimiting character
- On the most right column (Merseyside CH46 1LP) do another text to columns and choose " " as the delimiting character

That should be it. Then, if you want, you can patch the post code together again with =C2&D2

Done.

 

[dianeblackfamily]

Hi Joemo,
thanks for this code. A this has worked for the first 63 rows of data and then stopped. The first row that it has not extracted from is:
Messrs Placidi & Co, 31 Middle Road, Park Gate, Southampton SO31 7GH , which seems strange.
However, the macro gets a debug message highlighting c.Offset(0, 1).Value = vA(i) & " " & vA(i + 1) after stating Run time error 9
Subscript out of range, so this may have something to do with it?


Also for this address: Unit 9, M3 Trade Park, Manor Way, Eastleigh, Hampshire SO50 9YA it has extracted the postcode as M3 Trade