On Wed, 8 Feb 2006 11:26:40 -0800, "cj" wrote:

ron

this formula =MID(A1,FIND("cash",A1)-1,1)+MID(A1,FIND("office",A1)-1,1)
works fine if i enter in a1 : 3cash 2office 1train or 3cash 2office,but
returns with #value! error when i enter just 8cash or left blank for that day
off. with the regex formula i can enter "-" for the blank day and my totals
on the bottom still works and i can change and add combination of the words
cash|cs|office|of and i can add extra words after them "3cash training
2office(front).

I see what you mean. And I'm glad you've got something working for yourself.

To return a zero when the descriptor does not exist, you could also use a
formula like:

=REGEX.MID(A1&"0","\d(?=descriptor|$)")

or, for cash:

=REGEX.MID(A1&"0","\d(?=cash|$)")

or, to allow for the possibility of a <space between the number and the
descriptor:

=REGEX.MID(A1&"0","\d(?=\s*(descriptor|$))")





--ron