extracting numbers
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
|