On Fri, 8 May 2009 03:45:01 -0700, Francis <xlsmate(AT)gmail(DOT)com wrote:

A2 contain the following string :

** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008
NO. 2 INC $13,922.14

I want in B2 to add up the No which will give 5 on the above example
and in C2 give me the total amount of 45590.68

Is there a way to do this?

TIA

Harlan's answer, with regard to separate columns for the values, will be the
easiest to maintain.

If you must keep it together in a single string, you could download and install
Longre's free morefunc.xll add-in (Google to find a good download site), and
then use these **array-entered** formulas:

Sum of NO:

=EVAL(MCONCAT(--REGEX.MID(A1,"(?<=NO\.)\s*[-+]?\b[0-9,]*\.?[0-9]+\b",
INTVECTOR(REGEX.COUNT(
A1,"(?<=NO\.)\s*[-+]?\b[0-9,]*\.?[0-9]+\b",FALSE),1),FALSE),"+"))

Sum of $:

=EVAL(MCONCAT(--REGEX.MID(A1,"(?<=\$)[-+]?\b[0-9,]*\.?[0-9]+\b",
INTVECTOR(REGEX.COUNT(
A1,"(?<=NO\.)\s*[-+]?\b[0-9,]*\.?[0-9]+\b",FALSE),1),FALSE),"+"))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.
--ron