extracting and adding numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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
Click to expand...

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
 
hi ron

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

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

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
 
Back
Top