On Tue, 30 May 2006 13:39:01 -0700, Micah
wrote:

I have a spreadsheet with various words and numbers in each cell. The
arrangement of numbers in the string can vary. I want to extract from the
text string, the first 3 numbers as numbers.

Example

dog a cat 45672 223 12.42 mouse caught

I want to parse the above string (which is in a cell) and get
45672
223
12.42

One easy method is to download and install Longre's free morefunc.xll add-in
from http://xcell05.free.fr/

Then use regular expressions to define the numbers, and choose the first three
instances.

A regular expression which will define positive numbers of the form in your
example is: "(\d*\.)?\d+"

So the formula to extract the first instance would be:

=REGEX.MID($A$1,"(\d*\.)?\d+",ROWS($1:1))

The ROWS($1:1) subfunction identifies the instance (1=1st number, 2= 2nd
number, etc). So if you copy/drag down the formula, that function will
sequentially return 1, 2, etc as excel adjust the cell reference, giving you
the various instances.

If the number might be preceded by a "-" which you want to return, then use the
regex: "-?(\d*\.)?\d+" in place of the one in the above formula.


--ron