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 ($).

Hi
Thanks.
Agreed that the best is to parse into multiple columns and then do
the sum.
The solutions provided works. I was thinking that formula can't
handle these but you have proved me wrong.
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Harlan Grove" wrote:

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 ($).