extracting and adding numbers
 

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

extracting and adding numbers
 

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.

"cj" wrote in message
...
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

extracting and adding numbers
 

On Sat, 11 Feb 2006 01:36:27 -0800, "cj" wrote:

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

extracting and adding numbers
 

hi ron

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

"Ron Rosenfeld" wrote:

On Sat, 11 Feb 2006 01:36:27 -0800, "cj" wrote:

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

extracting and adding numbers
 

On Sun, 12 Feb 2006 02:37:29 -0800, "cj" wrote:

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