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

 

[RoryA]

Actually we regard "cross-posting" within the same forum as just duplicate posting and we delete the duplicates.

 

[Rick Rothstein]

Actually we regard "cross-posting" within the same forum as just duplicate posting and we delete the duplicates.

Okay, but I am guessing that you don't delete the duplicate until someone notices it is a duplicate and reports it, correct? Until that happens, if it happens at all, it is still possible for a solution to be posted to one of them and for someone reading the second one to waste their time developing the same or near-same solution, so I think the reference to the link so the OP will hopefully be deterred from doing it again still would apply.

 

[djmyers]

Rick:

I wanted to ask you about the formula you posted above:

=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)

I really like this formula, but can't seem to find a way to get it past its "known limitations" that you point out in your post.

Of particular interest to me is to be able to get it to deal with more than 14 digits in the input string. Do you have any suggestions?

Thanks!

 

[Scott T]

Excel has a limit of 15 digits after that changes the digit to zero

see
http://excelunplugged.com/2014/05/19/15-digit-limit-in-excel/

 

[Rick Rothstein]

Rick:

I wanted to ask you about the formula you posted above:

=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)

I really like this formula, but can't seem to find a way to get it past its "known limitations" that you point out in your post.

Of particular interest to me is to be able to get it to deal with more than 14 digits in the input string. Do you have any suggestions?

Unfortunately, a limit is just that... a limit. However, that limit is in Excel formula, not VBA code. Here is a UDF (user defined function) that will return all of the digits in any length text string (well, any length up to maximum of little more the 2 trillion characters that is) which is, of course, far more than Excel will let you put into a cell...

Function NumbersOnly(ByVal S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9]" Then Mid(S, X) = " "
  Next
  NumbersOnly = Replace(S, " ", "")
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NumbersOnly just like it was a built-in Excel function. For example,

=NumbersOnly(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[djmyers]

Rick,

Your UDF code works fabulously! Thanks!

How would I modify your UDF so that if a particular number is separated by a space, other text, etc., that the individual numbers will be separated by a space character.

In other words, this:

GNCP 638, 2014 Fall Placemats GFI , NORAD, 9/1-12/31/14

Becomes this:

638 2014 9 1 12 31 14

Thanks again!

 

[Rick Rothstein]

Rick,

Your UDF code works fabulously! Thanks!

How would I modify your UDF so that if a particular number is separated by a space, other text, etc., that the individual numbers will be separated by a space character.

In other words, this:

GNCP 638, 2014 Fall Placemats GFI , NORAD, 9/1-12/31/14

Becomes this:

638 2014 9 1 12 31 14

A simple fix in the last line of code...

Function NumbersOnly(ByVal S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9]" Then Mid(S, X) = " "
  Next
  NumbersOnly = Application.Trim(S)
End Function

 

[djmyers]

Works fabulously! Thank you, Rick!

 

[Rick Rothstein]

Works fabulously! Thank you, Rick!

You are welcome. I just had a thought... why not generalize the function to do both (more). Here is the result...

[table="width: 500"]
[tr]
	[td]Function NumbersOnly(ByVal S As String, Optional Delimiter As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9]" Then Mid(S, X) = " "
  Next
  NumbersOnly = Replace(Application.Trim(S), " ", Delimiter)
End Function[/td]
[/tr]
[/table]

What I have done here is add an optional argument that lets you specify an delimiter you want... if you omit the second argument like this...

=NumbersOnly(A1)

then it outputs the digits jammed up against one another like for your original request. However, if you specify a quoted text string for the second argument, then it will put that text between each group of separated numbers in the text. So for your space delimited request, you would use this...

=NumbersOnly(A1," ")

and to put, say, a slash-dash-slash between each number group, you would use this....

=NumbersOnly(A1,"/-/")

That should give you, or any reader of this thread, the flexibility they might need.

 

[djmyers]

This option is absolutely perfect.

This way I can specify the delimiters I want to use, rather than relying on my poor old tired eyes to flawlessly spot the space characters between the individual number sets.

Thank you again, Rick!