Extract 6 digit numbers from Street address

[arrowedman]

Hi,

I need help extracting 6 digit numbers from street address.

The problem is the format of the address is not always the same.
e.g. 65 ABERDEEN AVENUE DALLAS TEXAS 752307 UNITED STATES
7 Cecil Street Singapore 048547

Thank you!

 

[Ruddles]

Welcome to the forum!

Would a VBA solution be acceptable?

Would you know what to do with a module of VBA code if it was posted here?

 

[Sektor]

Function SixDigits(Str As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "\b\d{6}\b"
        SixDigits = .Execute(Str)(0)
    End With
End Function

 

[arrowedman]

I am no expert in excel. Especially on VB

Any help will be greatly appreciated.

Welcome to the forum!

Would a VBA solution be acceptable?

Would you know what to do with a module of VBA code if it was posted here?

 

[Ruddles]

Looks like Sektor beat me to it!

Open your worksheet and press Alt-F11 to open Microsoft Visual Basic, press Ctrl-R to view the Project Explorer, then go Insert > Module.
A new 'standard' code module will appear under Modules, probably called Module1. You might need to click the + symbol against Modules to expand the group.

Double-click the name of this new module to open it. Remove any code you find in the code window (probably only the words Option Explicit, if anything) and paste Sektor's code in its place.

Return to your worksheet. Assuming your address is in A1, enter this formula in B1:-

=SixDigits(A1)

Is that what you want?

 

[arrowedman]

Hi All,

Thanks for the help.

But I encountered the error: "Ambiguous name detected: SixDigits"

 

[Rick Rothstein]

Assuming there will be no numbers after that 6-digit number and assuming the leading number in you text will never be more than 6-digits long, give this formula a try...

=MID(A1,6+MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(A1,7,999)&"0123456789")),6)

 

[Rick Rothstein]

Assuming there will be no numbers after that 6-digit number and assuming the leading number in you text will never be more than 6-digits long, give this formula a try...

=MID(A1,6+MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(A1,7,999)&"0123456789")),6)

Actually, if you are uncomfortable with it, we can remove that last assumption. The following formula only requires that 6-digit number to be the last number appearing in the address...

=MID(A1,FIND(" ",A1)+MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(A1,FIND(" ",A1)+1,999)&"0123456789")),6)

 

[arrowedman]

Thanks, Rick.

I tried the formula on the below address:

28 JALAN SS19/36 DAMANSARA 47400 Pahang D.E.M'SIA

The result that I have is 19/36

Assuming there will be no numbers after that 6-digit number and assuming the leading number in you text will never be more than 6-digits long, give this formula a try...

=MID(A1,6+MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(A1,7,999)&"0123456789")),6)

 

[Rick Rothstein]

Thanks, Rick.

I tried the formula on the below address:

28 JALAN SS19/36 DAMANSARA 47400 Pahang D.E.M'SIA

The result that I have is 19/36

That is because your examples didn't show more than 2 sets of numbers in the address and your current example has 3 or 4 depending on how you count them. On top of that, and this is critical in being able to straighten things out, you told us there would be a 6-digit number in your address and your example does not have one. If that is a correct address, then you will need to tell us the exact parameters for the number we are to be searching for. Also, is the number we are to be searching for always the last number in the address?