Extracting Multiple Numbers from String

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:

Hope that helps.

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

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

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.

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!

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.


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

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

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>

Domenic said:

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!

Click to expand...

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