On Mon, 15 Jan 2007 05:59:01 -0800, enyaw
wrote:

There may be more than two items in the cell. The data will always be
word/space/number/space/word/space/number and the same for any more items
added.

Here's one way, then.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then you can use one of these two formulas (I'm not sure which one is faster).
These formulas use Regular Expressions to extract the numeric values from the
strings.

They will extract all numbers.

If you might have a number in the "word" portion, that you wish to have
ignored, (e.g. wo23rd 658 wo5rd 123) and you want to ignore the 23 and the 5,
but add the 658 and 123, some further changes will be required in the "Regex".



The **array** formula:

=SUM(--REGEX.MID(A1,"\d+",INTVECTOR(REGEX.COUNT(A1,"\d+") ,1)))

(To enter an array formula, hold down <ctrl<shift when you hit <enter.
Excel will place braces {...} around the formula).

Or the non-array formula:

=EVAL(MCONCAT(REGEX.MID(A1,"\d+",INTVECTOR(REGEX.C OUNT(A1,"\d+"),1)),"+"))

You did not answer my question as to whether the numbers would be integers or
not. The above formula will work for integers. If the values may include
decimals, and/or be positive or negative, then instead of "\d+" you should
substitute the following:

"[-+]?(\d*\.)?\d+"

which would result in

**array entered**:

=SUM(--REGEX.MID(A1,"[-+]?(\d*\.)?\d+",
INTVECTOR(REGEX.COUNT(A1,"[-+]?(\d*\.)?\d+"),1)))

OR normally entered:

=EVAL(MCONCAT(REGEX.MID(A1,"[-+]?(\d*\.)?\d+",
INTVECTOR(REGEX.COUNT(A1,"[-+]?(\d*\.)?\d+"),1)),"+"))





--ron

On Mon, 15 Jan 2007 10:15:10 -0500, Ron Rosenfeld
wrote:

On Mon, 15 Jan 2007 05:59:01 -0800, enyaw
wrote:

There may be more than two items in the cell. The data will always be
word/space/number/space/word/space/number and the same for any more items
added.

Here's one way, then.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then you can use one of these two formulas (I'm not sure which one is faster).
These formulas use Regular Expressions to extract the numeric values from the
strings.

They will extract all numbers.

If you might have a number in the "word" portion, that you wish to have
ignored, (e.g. wo23rd 658 wo5rd 123) and you want to ignore the 23 and the 5,
but add the 658 and 123, some further changes will be required in the "Regex".



The **array** formula:

=SUM(--REGEX.MID(A1,"\d+",INTVECTOR(REGEX.COUNT(A1,"\d+") ,1)))

(To enter an array formula, hold down <ctrl<shift when you hit <enter.
Excel will place braces {...} around the formula).

Or the non-array formula:

=EVAL(MCONCAT(REGEX.MID(A1,"\d+",INTVECTOR(REGEX. COUNT(A1,"\d+"),1)),"+"))

You did not answer my question as to whether the numbers would be integers or
not. The above formula will work for integers. If the values may include
decimals, and/or be positive or negative, then instead of "\d+" you should
substitute the following:

"[-+]?(\d*\.)?\d+"

which would result in

**array entered**:

=SUM(--REGEX.MID(A1,"[-+]?(\d*\.)?\d+",
INTVECTOR(REGEX.COUNT(A1,"[-+]?(\d*\.)?\d+"),1)))

OR normally entered:

=EVAL(MCONCAT(REGEX.MID(A1,"[-+]?(\d*\.)?\d+",
INTVECTOR(REGEX.COUNT(A1,"[-+]?(\d*\.)?\d+"),1)),"+"))





--ron


And if numbers may be in the "word"s, then try this regex instead:

"[-+]?\b(\d*\.)?\d+\b"


--ron

On Mon, 15 Jan 2007 10:31:45 -0500, Ron Rosenfeld
wrote:




And if numbers may be in the "word"s, then try this regex instead:

"[-+]?\b(\d*\.)?\d+\b"



I should amplify that the above regex will "ignore" numbers that are embedded
within words. The original variations will "include" those numbers.

--ron