I see in your earlier post this is for a timesheet.
I would highly recommend you redesign things
so that you don't have to use such "hacked" formulas
to account for time worked.

My suggestion still stands!

Use *1* cell for hours worked and *1* cell for the dept.

Biff

"cj" wrote in message
...
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(a 1&"0","(\D+)","+")),""))

a3=IF(REGEX.FIND("cash",a1)=1,8,IF(REGEX.FIND(a1," cash|office|pay|cs|off|py")0,EVAL(REGEX.SUBSTITUT E(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?

thanks

"Ron Rosenfeld" wrote:

On Tue, 7 Feb 2006 12:59:29 -0800, "cj"
wrote:

one last specification for the formula,if column a1 is "3cash 4office
1train"
,i want a2 which is the cash|office hrs column to add only the 3cash
4office
is = 7 and a3 which is the train|hiring|other hrs column to add only the
1train is = 1.
i have 6 different hrs columns with 3 or more different depts.
the order of depts can vary in order and combination.
eg)

a1 shift worked column a1= 3cash 4office 1other
a1=3stock
2train 2pay
a2 cash|office|pay column a2= 7
a2=2
a3 stock|train|rec column a3= 0
a3=5
a4 other|front|ret|rel column a4=1
a4=0

If this is all you've wanted, we've wasted a lot of time.

In your first post, you indicated that the numbers would be 1-8.

And you've always had the strings formatted with no space between the
number
and the description.

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