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

"Ron Rosenfeld" wrote:

On Tue, 7 Feb 2006 20:48:13 -0800, "cj" wrote:

hi ron

thanks for your answers and patience, i really appreciate your help.
i play with the formulas

a2=IF(REGEX.FIND("stock",a1)=1,8,IF(REGEX.FIND(a1 ,"stock|train|rec|st|tr")0,EVAL(REGEX.SUBSTITUTE( a1&"0","(\D+)","+")),""))

a3=IF(REGEX.FIND("cash",a1)=1,8,IF(REGEX.FIND(a1, "cash|office|pay|cs|off|py")0,EVAL(REGEX.SUBSTITU TE(a1&"0","(\D+)","+")),""))

these 2 formulas work great: when i enter in a1 just "cash" a2 returns with
8 and same for cash. thats what i want and i can add extra words if the shift
is not 8hrs eg: 3cash 2office 2pay. up to this point everything is fine
except when a1 has both a2 and a3 depts eg: a1 is " 3cash 2office 2stock",
both a2 and a3 return with 8. its there any way can separate them. i need a2
to return with 2 and a3 to return with 5?

Reread my last post and use the formulas there. For what you've finally
described, you don't need morefunc or the REGEX functions at all.

===========================
All you need to do to pull out the number associated with a particular
descriptor is:

=MID(A1,FIND(descriptor,A1)-1,1) or, for the office:

=MID(A1,FIND("office",A1)-1,1)

And you can use the add operator to add these together.

So if you wanted to add only the cash and office from a string

"3cash 4office 1train"

all you need to do is:

=MID(A1,FIND("cash",A1)-1,1)+MID(A1,FIND("office",A1)-1,1)


You should be able to figure out the rest by yourself.
==============================
--ron