extracting numbers

On Mon, 6 Feb 2006 17:18:28 -0800, "cj" wrote:

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

On Mon, 06 Feb 2006 21:17:25 -0500, Ron Rosenfeld
wrote:

On Mon, 6 Feb 2006 17:18:28 -0800, "cj" wrote:

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

This is one way:

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

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


--ron

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" wrote:

On Mon, 06 Feb 2006 21:17:25 -0500, Ron Rosenfeld
wrote:

On Mon, 6 Feb 2006 17:18:28 -0800, "cj" wrote:

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

This is one way:

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

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


--ron

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

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

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

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

On Tue, 7 Feb 2006 20:48:13 -0800, "cj" wrote:

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.SUBSTITU TE(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

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" wrote:

On Tue, 7 Feb 2006 20:48:13 -0800, "cj" wrote:

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.SUBSTITU TE(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

On Wed, 8 Feb 2006 11:26:40 -0800, "cj" wrote:

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