extracting numbers

[Ron Rosenfeld]

oops, didn't install morefunc.
it works now, thanks.
is there any way i can include the words cash|office|stock to make a2 add up
the numbers only if any of those 3 dept are entered in a1?

Yes, there is.

But do you have any more specifications?
--ron

 

[Ron Rosenfeld]

Yes, there is.

But do you have any more specifications?
--ron

This is one way:

=IF(REGEX.FIND(A1,"cash|office|stock")>0,EVAL(REGEX.SUBSTITUTE(A1&"0","(\D+)","+")),"")

But it'll be easiest if you could lay out all of your specifications at once,
for your project.


--ron

 

[Guest]

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

 

[Ron Rosenfeld]

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

 

[Guest]

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(a1&"0","(\D+)","+")),""))

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

 

[Biff]

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

 

[Ron Rosenfeld]

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(a1&"0","(\D+)","+")),""))

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

Reread my last post and use the formulas there. For what you've finally
described, you don't need morefunc or the REGEX functions at all.

===========================
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

 

[Guest]

ron

this formula =MID(A1,FIND("cash",A1)-1,1)+MID(A1,FIND("office",A1)-1,1)
works fine if i enter in a1 : 3cash 2office 1train or 3cash 2office,but
returns with #value! error when i enter just 8cash or left blank for that day
off. with the regex formula i can enter "-" for the blank day and my totals
on the bottom still works and i can change and add combination of the words
cash|cs|office|of and i can add extra words after them "3cash training
2office(front).

 

[Ron Rosenfeld]

ron

this formula =MID(A1,FIND("cash",A1)-1,1)+MID(A1,FIND("office",A1)-1,1)
works fine if i enter in a1 : 3cash 2office 1train or 3cash 2office,but
returns with #value! error when i enter just 8cash or left blank for that day
off. with the regex formula i can enter "-" for the blank day and my totals
on the bottom still works and i can change and add combination of the words
cash|cs|office|of and i can add extra words after them "3cash training
2office(front).

I see what you mean. And I'm glad you've got something working for yourself.

To return a zero when the descriptor does not exist, you could also use a
formula like:

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

or, for cash:

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

or, to allow for the possibility of a <space> between the number and the
descriptor:

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





--ron