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

 

[schielrn]

Welcome to the Board!

Since all the numbers are not together I believe this can only be done with a user-defined function and not with excel's native functions. You can use this in a module:

'--------------------------------------------------------------------------------------------------------
'***NEW FUNCTION***
'Description: Separates Alphabetic and Numeric Data.  (i.e. Alpha 123, Alpha123)
'             True or 1 will return alphabet, False or 0 will return numeric
'Example:   A1 value is Rob12Schiele34
'           =Sep(A1,1)      Returns:    RobSchiele
'           =Sep(A1,0)      Returns:    1234
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

Hope that helps.

 

[Cbrine]

I can put together some vba code to do this, but I'm wondering how you can use the results?

1409250
could be
140 9250
1 409250
1409 250
14902 50
149025 0

Just Asking

 

[bolillo1234]

Schielrn:

Where do I enter that information ?

Cbrine:

I am comparing the information with another database and will just use it as one number.

1409250

Thank you both.

Bolillo

 

[schielrn]

Press Alt+F11 and then press Alt, I, then M. This will insert a module. Paste this code in there and then use the formula example I have given in the description.

Hope that helps and post back with any other questions.

 

[Domenic]

Here's a formula approach...

=SUM(MID(0&A2,LARGE(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))*ROW(INDIRECT("1:"&LEN(A2))),ROW(INDIRECT("1:"&LEN(A2))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A2)))/10)

...where A2 contains the text string of interest. Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Cbrine]

I'm not sure how much data you are working with, and I'm assuming you are doing some type of validation between the two sets of data, but you may run into the following issue.

123N 4567 W
12West 34567 Johnston Blvd

They would both evaluate to 1234567, which on a straight link between the tables would cause a circadian join, and possiblity mess up your results. I would suggest you do a count of each final numbers and compare the counts. This way you would see.

F1             F2      F3     F4
12334567    2       2       Equal

Don't know if this would be an issue or not, but thought I would mention it.
HTH
Cal

 

[mgirvin]

Domenic,

Someone sent me the link to this post and I used you amazing formula. Do you think that you could read post #8 that I made? I tried to list how I understand the formula. Could you read it and see if I got that concepts correct? The post I made is here:

http://www.mrexcel.com/forum/showthread.php?p=2193803&posted=1#post2193803

 

[abbotti]

Hi I’m a Newbie here to the board. I tried the code above and it works great. I have a little variation to the above string and my skills are limited in figuring it out. An example of my string is... in Cell G1, “C2_PU07_15.04.2013_633759 John Henry” the number I need is after the _ “633759” I currently use text to columns to break it out but I know there is an easier way. Thanks for your consideration.</SPAN>

 

[Rick Rothstein]

Here's a formula approach...

=SUM(MID(0&A2,LARGE(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))*ROW(INDIRECT("1:"&LEN(A2))),ROW(INDIRECT("1:"&LEN(A2))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A2)))/10)

...where A2 contains the text string of interest. Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

And here is another array-entered formula that has been posted in the past by Lars-Åke Aspelin...

=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)

This is an array formula and has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER.

It has the following (known) limitations:

- The input string in cell A1 must be shorter than 300 characters

- There must be at most 14 digits in the input string. (Any following digits will be shown as zeroes.)

Maybe of no practical use, but it will also handle the following two cases correctly:

- a "0" as the first digit in the input will be shown correctly in the output

- an input without any digits at all will give the empty string as output (rather than 0).