On Sun, 5 Feb 2006 19:26:28 -0800, "cj" wrote:

thanks ron for your answers

how do i expand the formula to extract and add numbers in different
combinations and order?
eg:
a1: "3cash" "4stock" "5train" "3cash 4stock" "4train 3cash 1stock"

a2: 3 4 5 7
8

Try this:

=EVAL(REGEX.SUBSTITUTE(A1&"0","(\D+)","+"))

First we add a zero to the end of the string. You'll see why in a minute.

Now your phrases consist of digits followed by characters that are NOT digits.
The regex "{\D+)" says replace sets of characters that are not Digits with a
"+". The string of "not digits" can be 1 to many characters long.

So with "4train 3cash 1stock", after the substitutions, we have 4+3+1+0.

The EVAL function is also from morefunc.xll. I've not used it before but it
"Evaluates a formula or expression that is in the form of text and returns the
result."

So we've constructed the text and then apply the EVAL function to get the
result you want.

Let me know if it works out OK.


--ron