extracting and adding numbers

[Guest]

i.m looking for a formula to extract and add hrs in a time sheet.

if a1 is 3cash 2office 2demo
i need a2(cash|office|pay) column to add the 3cash and 2office and
a3(demo|cs|tr) to extract the 2hrs in demo

the orders and combination can change and it could be just 1 dept
eg)a1 is 8pay then a2(cash|office|pay) = 8 and a3(demo|cs|tr) = 0
a1 is 5office 2demo then a2(cash|office|pay) = 5 and a3(demo|cs|tr) = 2
a1 is 2demo 4cash 3tr then a2(cash|office|pay) = 4 and a3(demo|cs|tr)
= 5

any suggestions?

thanks

 

[Barb Reinhardt]

It appears that you have multiple pieces of information in the cells. You
might want to consider using the SEARCH function to find where "cash"
"office" or "pay" are and use the MID function to get the dates.

 

[Ron Rosenfeld]

i.m looking for a formula to extract and add hrs in a time sheet.

if a1 is 3cash 2office 2demo
i need a2(cash|office|pay) column to add the 3cash and 2office and
a3(demo|cs|tr) to extract the 2hrs in demo

the orders and combination can change and it could be just 1 dept
eg)a1 is 8pay then a2(cash|office|pay) = 8 and a3(demo|cs|tr) = 0
a1 is 5office 2demo then a2(cash|office|pay) = 5 and a3(demo|cs|tr) = 2
a1 is 2demo 4cash 3tr then a2(cash|office|pay) = 4 and a3(demo|cs|tr)
= 5

any suggestions?

thanks

What went wrong with the previous suggestions?

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

e.g: For cash and office:

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


--ron

 

[Guest]

hi ron

thanks alot for your help, i didn't put the + between the 2 descriptors,
formula works great now. thanks again

 

[Ron Rosenfeld]

hi ron

thanks alot for your help, i didn't put the + between the 2 descriptors,
formula works great now. thanks again

Good. I'm glad it seems to be working. If you should require more assistance,
please post back in the same thread (if you can) -- that makes it simpler for
me to follow up.

Best,

--ron