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?
....

Like it or not, the best way to handle this sort of thing would be to
parse such strings (records) into multiple columns (fields), then sum
the numbers.

You could parse and sum in single formulas, but it's nowhere near
simple and very inefficient. Here's one way. Define a name like seq
referring to the formula

=ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,255,1))

Then if your string above were in a cell named x, use the array
formula

=SUM(--IF(seq<=(LEN(x)-LEN(SUBSTITUTE(x,"NO. ","")))/4,
LEFT(MID(x,SMALL(IF(MID(x,seq,4)="NO. ",seq),seq)+4,6),
FIND(" ",MID(x&" ",SMALL(IF(MID(x,seq,4)="NO. ",seq),seq)+4,6)))))

to sum the numbers following the substring "NO. " in your full string/
record, and use the array formula

=SUM(--IF(seq<=LEN(x)-LEN(SUBSTITUTE(x,"$","")),
LEFT(MID(x,SMALL(IF(MID(x,seq,1)="$",seq),seq)+1,1 2),
FIND(":",MID(x&":",SMALL(IF(MID(x,seq,1)="$",seq), seq)+1,12))-1)))

to sum the amounts following the dollar signs ($).