Extracting a number out of a sentence

Hi I am looking for an equation or macro that can extract numbers out of a sentence.

Eg, 'There were 300 children in a school, and 10 classes of children' I need to extrcat the 300 and the 10 into 2 cells.

There may be more than 2 numbers in longer sentences. Is there anyway to do this?

Thanks in advance

If you download and install the free addin Morefunc.xll from here:

http://download.cnet.com/Morefunc/30...-10423159.html


then you can use this formula:

=SUBSTITUTE(TRIM(REGEX.SUBSTITUTE(A1,"[^0-9]"," "))," ",",") copied down

which extracts the numbers and separates them with a comma.

or if you want them in separate cells, then use formula

=TRIM(REGEX.SUBSTITUTE(A1,"[^0-9]"," ")) copied down

and then select the column, go to Data|Text to Columns and select Delimited, click Next, select Space, Click Finish..

while were on reg ex why not
=REGEX.MID($A1,"\b([0-9]|[1-9][0-9]|[1-9][0-9][0-9])\b",1,FALSE)
which will extract any digit group fron 0-999 the numer in magenta is the instance so for the second change to 2 or use
=REGEX.MID($A1,"\b([0-9]|[1-9][0-9]|[1-9][0-9][0-9])\b",COLUMNS($A$1:A1),FALSE) which can be dragged across and down

If your posted example is typical:

Please Login or Register  to view this content.

This formula pulls the first number:

Please Login or Register  to view this content.

and this one pulls the second:

Please Login or Register  to view this content.

Using the example...
B1 returns: 300
C1 returns: 10

Is that something you can work with

Originally Posted by Ron Coderre

If your posted example is typical:

Please Login or Register  to view this content.

This formula pulls the first number:

Please Login or Register  to view this content.

and this one pulls the second:

Please Login or Register  to view this content.

Using the example...
B1 returns: 300
C1 returns: 10

Is that something you can work with

I figured, based on this comment:

There may be more than 2 numbers in longer sentences

that it would be easier to do as I prescribed.... instead of having a big formula going across the sheet to cover all possible combinations...

Originally Posted by NBVC

I figured, based on this comment:
that it would be easier to do as I prescribed.... instead of having a big formula going across the sheet to cover all possible combinations...

Good point. I agree.
If only "regular expressions" were intuitive...instead of looking like compu-gibberish

Originally Posted by Ron Coderre

Good point. I agree.
If only "regular expressions" were intuitive...instead of looking like compu-gibberish

Amen to that... that is why I limit my regex suggestions... I have to figure it all out first...which takes a lot of time/practice.. for sure

Well to get the 3rd, using Ron's Method... you would need to nest the Substitute() function:

Please Login or Register  to view this content.

and the 4th, yet another nested Substitute...

Please Login or Register  to view this content.

Hopefully, you can see the pattern.....

... but you will quickly get an big ugly formula, and unless you are in 2007, you will only have a limited number of nestings you can do...

..or.. maybe Ron can write you a UDF perhaps including the Regex functionality.

If only "regular expressions" were intuitive...instead of looking like compu-gibberish

look fine to me lol
i just use a site that has them already done then modify as needed!

Originally Posted by martindwilson

look fine to me lol
i just use a site that has them already done then modify as needed!

care to share?

no prob
http://www.regular-expressions.info/examples.html
also to test them out
http://www.rubular.com/

Thanks Martin.

Sorry - Read post below - forgot to attach!

Hi, I'm struggling with the equations now - think I'm just rubbish. But I have attached an example, but some of the numbers arn't coming out, and some are being completely missed!

Any ideas?

This is happening because when a returned value ends with a 0 after the decimal point, it cuts that 0 off and when column B or C substitute that number, in the original text, then all the digits before the last 0 are substituted only, leaving a 0 behind in the original text and so the formula thinks that lone 0 is a number and extracts it...

To resolve that, convert the number to replace to a text string forcing 2 decimal places.. so that the full number gets subbed including any possible ending 0... harder to explain than to see...

anyways, replace formula in B1 with:

Please Login or Register  to view this content.

and formula in C1 with:

Please Login or Register  to view this content.

and copy them down.

Note the red parts are the "fixed" parts...

Also note, that you have many of these, the process may be slow...

one way to reduce the slowness is to change the 1000 in the formulas to a number that represents the longest a string in column A may be... for example 200 characters?.. I used 255 in the attached sheet and it got all the numbers required faster...