Extracting Multiple Numbers from String

[bolillo1234]

I have thousands of fields that contain addresses and I need to extract only the numbers. The problem I am facing is that there are two numbers within the string and they begin at different times.

Examples of the data
1409 N 250 W
259 West 158 Johnson Blvd
109829 South 344 East

What Im trying to get
1409250
259158
109829344

Thanks,
Bolillo

 

[Peter_SSs]

Examples of My string are :

1. Asia Pacific Billing ATT-14-015(eForm # 2028264)

2. Asia Pacific Billing ATT-14-018v2(eForm # 2029389)

3. Asia Pacific Billing -eForm # 2024585

If they are representative of your data (always 7 digits & always at the end of the string apart from possibly a ")"), then you may also be able to use just this.

Excel Workbook
	A	B
1	Asia Pacific Billing ATT-14-015(eForm # 2028264)	2028264
2	Asia Pacific Billing ATT-14-018v2(eForm # 2029389)	2029389
3	Asia Pacific Billing -eForm # 2024585	2024585
Extract No

 

[Peter_SSs]

.. otherwise if there could be other text afterwards &/or other # symbols before the eForm, but the required numbers are still always 7 digits, then:

Excel Workbook
	A	B
1	Asia Pacific Billing ATT-14-015(eForm # 2028264)	2028264
2	Asia Pacific Billing ATT-14-018v2(eForm # 2029389)	2029389
3	Asia Pacific Billing -eForm # 2024585	2024585
4	ABC#ATT-14-015(eForm # 2028264) extra	2028264
Extract No

 

[FlameRetired]

@manav1103,

Can you supply a comprehensive list of the countries for a lookup table in either thread?

Extracting Numbers Out of String

 

[Peter_SSs]

@manav1103 again,
Please read #13 of the Forum Rules, including the page linked at the end of that rule.

 

[bhos123]

I have thousands of fields that contain addresses and I need to extract only the numbers. The problem I am facing is that there are two numbers within the string and they begin at different times.

Examples of the data
1409 N 250 W
259 West 158 Johnson Blvd
109829 South 344 East

What Im trying to get
1409250
259158
109829344

Thanks,
Bolillo

Try this until length of the string : =IF(ISERR(MID(A8,1,1)*1>0),"",MID(A8,1,1))&IF(ISERR(MID(A8,2,1)*1>0),"",MID(A8,2,1))&IF(ISERR(MID(A8,3,1)*1>0),"",MID(A8,3,1))&IF(ISERR(MID(A8,4,1)*1>0),"",MID(A8,4,1))&IF(ISERR(MID(A8,5,1)*1>0),"",MID(A8,5,1))&IF(ISERR(MID(A8,6,1)*1>0),"",MID(A8,6,1))&IF(ISERR(MID(A8,7,1)*1>0),"",MID(A8,7,1))&IF(ISERR(MID(A8,8,1)*1>0),"",MID(A8,8,1))

 

[Peter_SSs]

Try this until length of the string : =IF(ISERR(MID(A8,1,1)*1>0),"",MID(A8,1,1))&IF(ISERR(MID(A8,2,1)*1>0),"",MID(A8,2,1))&IF(ISERR(MID(A8,3,1)*1>0),"",MID(A8,3,1))&IF(ISERR(MID(A8,4,1)*1>0),"",MID(A8,4,1))&IF(ISERR(MID(A8,5,1)*1>0),"",MID(A8,5,1))&IF(ISERR(MID(A8,6,1)*1>0),"",MID(A8,6,1))&IF(ISERR(MID(A8,7,1)*1>0),"",MID(A8,7,1))&IF(ISERR(MID(A8,8,1)*1>0),"",MID(A8,8,1))

Welcome to the MrExcel board!

Do you realise that you are responding to somebody who asked their question more than 7 years ago!

 

[materhaar]

The VBA code extract multiple numbers only works on windows. Is there a similar VBA code for mac? Please find below the windows code:

Function Sep(txt As String, flg As Boolean) As String
With CreateObject("VBScript.RegExp")
.Pattern = IIf(flg = True, "\d+", "\D+")
.Global = True
Sep = .Replace(txt, "")
End With
End Function

 

[materhaar]

This VBA code above works only on windows. Is there a similar VBA code for Mac?

 

[Rick Rothstein]

The VBA code extract multiple numbers only works on windows. Is there a similar VBA code for mac? Please find below the windows code:

Function Sep(txt As String, flg As Boolean) As String
With CreateObject("VBScript.RegExp")
.Pattern = IIf(flg = True, "\d+", "\D+")
.Global = True
Sep = .Replace(txt, "")
End With
End Function

Does this work for you...

Function Sep(txt As String, flg As Boolean) As String
  Dim X As Long, Pattern As String
  Pattern = "[" & IIf(flg, "!", "") & "0-9]"
  For X = 1 To Len(txt)
    If Mid(txt, X, 1) Like Pattern Then Sep = Sep & Mid(txt, X, 1)
  Next
End Function

 

[materhaar]

Does this work for you...

Function Sep(txt As String, flg As Boolean) As String
  Dim X As Long, Pattern As String
  Pattern = "[" & IIf(flg, "!", "") & "0-9]"
  For X = 1 To Len(txt)
    If Mid(txt, X, 1) Like Pattern Then Sep = Sep & Mid(txt, X, 1)
  Next
End Function

Many thanks Rick! This works perfect!