On Fri, 8 May 2009 14:10:59 -0700 (PDT), Harlan Grove
wrote:

Ron Rosenfeld wrote...
...
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),"+"))

I had wanted to avoid regexps, but if you're going to use 'em, use 'em
efficiently. Either

=SUMPRODUCT(--REGEX.MID(x,"(?<=NO\. )\d+",INTVECTOR(REGEX.COUNT(x,"NO
\. "),1)))

or

=EVAL(REGEX.SUBSTITUTE(x&"NO. 0",".*?NO\. (\d+)","+[1]"))

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),"+"))

Either

=SUMPRODUCT(--REGEX.MID(x&":","(?<=\$)[0-9,.]+(?=:)",
INTVECTOR(REGEX.COUNT(x&":","\$[0-9,.]+:"),1)))

or

=EVAL(SUBSTITUTE(REGEX.SUBSTITUTE(x&":",".*?\$
([0-9,.]+):","+[1]"),",",""))

Much better. Thanks.
--ron