i need a formula to extract numbers.

if cell a1 is "3cash 5stock", i want to extract the number "3" to cell a2
and extract the number "5" to cell a3. the number in cash stock can vary
between 1 to 8 and vice versa.

can some help

Assuming that you have one digit in front of cash separated by a space
a single digit followed by stock --- so how does vice versa fit in.

a1: 3cash 5 stock
b1: =left(A1,1) ---- the length of 1 is optional for LEFT Worksheet Function
c1: =mid(a1,7,1)

or if you want numbers
b1: =value(left(a1,1))
c1: =value(mid(a1,7,1))

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"cj" wrote ...
i need a formula to extract numbers.

if cell a1 is "3cash 5stock", i want to extract the number "3" to cell a2
and extract the number "5" to cell a3. the number in cash stock can vary
between 1 to 8 and vice versa.

Hi!

Enter this formula in A2:

=IF(ISERROR(LEFT(A1)*1),"",LEFT(A1)*1)

Enter this formula in A3:

=IF(ISERROR(MID(A1,FIND(" ",A1)+1,1)*1),"",MID(A1,FIND(" ",A1)+1,1)*1)

Biff

"cj" wrote in message
...
i need a formula to extract numbers.

if cell a1 is "3cash 5stock", i want to extract the number "3" to cell a2
and extract the number "5" to cell a3. the number in cash stock can vary
between 1 to 8 and vice versa.

can some help

On Sat, 4 Feb 2006 19:58:21 -0800, "cj" wrote:

i need a formula to extract numbers.

if cell a1 is "3cash 5stock", i want to extract the number "3" to cell a2
and extract the number "5" to cell a3. the number in cash stock can vary
between 1 to 8 and vice versa.

can some help

If the values are always in that format, then:

a2: =LEFT(A1,FIND("cash",A1)-1)
A3: =MID(A1,FIND("cash",A1)+5,FIND("stock",A1)-FIND("cash",A1)-5)


--ron

thanks guys for the answer!

all 3 differrent formulas worked! i also need cell a2 to extract value of 8
if a1 is just "cash", value of 7 if a1 is "7cash",value of 6 if a1 is
"6cash", and so on and same for a3 to extract 8 if a1 is just "stock", etc.
and what if the order is reveresed from 3cash 4stock to 4stock 3cash?

plz help

"Biff" wrote:

Hi!

Enter this formula in A2:

=IF(ISERROR(LEFT(A1)*1),"",LEFT(A1)*1)

Enter this formula in A3:

=IF(ISERROR(MID(A1,FIND(" ",A1)+1,1)*1),"",MID(A1,FIND(" ",A1)+1,1)*1)

Biff

"cj" wrote in message
...
i need a formula to extract numbers.

if cell a1 is "3cash 5stock", i want to extract the number "3" to cell a2
and extract the number "5" to cell a3. the number in cash stock can vary
between 1 to 8 and vice versa.

can some help

thanks guys for the answer!

all 3 differrent formulas worked! i also need cell a2 to extract value of 8
if a1 is just "cash", value of 7 if a1 is "7cash",value of 6 if a1 is
"6cash", and so on and same for a3 to extract 8 if a1 is just "stock", etc.
and what if the order is reveresed from 3cash 4stock to 4stock 3cash?

plz help



"Ron Rosenfeld" wrote:

On Sat, 4 Feb 2006 19:58:21 -0800, "cj" wrote:

i need a formula to extract numbers.

if cell a1 is "3cash 5stock", i want to extract the number "3" to cell a2
and extract the number "5" to cell a3. the number in cash stock can vary
between 1 to 8 and vice versa.

can some help

If the values are always in that format, then:

a2: =LEFT(A1,FIND("cash",A1)-1)
A3: =MID(A1,FIND("cash",A1)+5,FIND("stock",A1)-FIND("cash",A1)-5)


--ron

Hi!

Is it possible to have entries like these:

cash 4stock
5cash stock
stock cash

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. Things would be much easier if you enter hours
worked in one cell and the dept in another cell.

Biff

"cj" wrote in message
...
thanks guys for the answer!

all 3 differrent formulas worked! i also need cell a2 to extract value of
8
if a1 is just "cash", value of 7 if a1 is "7cash",value of 6 if a1 is
"6cash", and so on and same for a3 to extract 8 if a1 is just "stock",
etc.
and what if the order is reveresed from 3cash 4stock to 4stock 3cash?

plz help

"Biff" wrote:

Hi!

Enter this formula in A2:

=IF(ISERROR(LEFT(A1)*1),"",LEFT(A1)*1)

Enter this formula in A3:

=IF(ISERROR(MID(A1,FIND(" ",A1)+1,1)*1),"",MID(A1,FIND(" ",A1)+1,1)*1)

Biff

"cj" wrote in message
...
i need a formula to extract numbers.

if cell a1 is "3cash 5stock", i want to extract the number "3" to cell
a2
and extract the number "5" to cell a3. the number in cash stock can
vary
between 1 to 8 and vice versa.

can some help

Did you look at Biff's answer which pointed you back to his original answer, depending
on what you want you may want the parts in separated cells, when you posted your same
response earlier. If you can't figure it out you will have to be more specific about what
have in Column A and what you want in Column B as the answer or in Cols B & C as the answers.

"David McRitchie" wrote:

Did you look at Biff's answer which pointed you back to his original answer, depending
on what you want you may want the parts in separated cells, when you posted your same
response earlier. If you can't figure it out you will have to be more specific about what
have in Column A and what you want in Column B as the answer or in Cols B & C as the answers.

is it possible for me to enter mulptiple formulas in one column?
somedays i need people to work 8 hours cash and stock and for them to work
cash for the first 3 hours and 4 hours stock after. Certain days i need them
to work to work stock for the first 4 hours and 3 hours cash after.

if column A is 3cash 4stock 4stock 4cash 6cash
5stock
(cash hrs)B would = 3 4 8
0
(stockhrs)C would = 4 4 6
5

i don't think this can be done, i have 8 differrent depts and i want each
dept column to extract their hours. like cash would extract its hours from
column A if there is a cash shift in it, like the example above.

try this in your cash column
=IF(ISERROR(SEARCH("cash",$A1)),0,IF(SEARCH("cash" ,$A1)=1,8,IF(AND(SEARCH("cash",$A1)=2,LEN($A1)<6), LEFT($A1,1),MID($A1,SEARCH("cash",$A1,1)-1,1))))
and this in your stock column
=IF(ISERROR(SEARCH("stock",A1)),0,IF(SEARCH("stock ",A1)=1,8,IF(AND(SEARCH("stock",A1)=2,LEN(A1)<=6), LEFT(A1,1),MID(A1,SEARCH("stock",A1,1)-1,1))))
--
paul
remove nospam for email addy!



"cj" wrote:



"David McRitchie" wrote:

Did you look at Biff's answer which pointed you back to his original answer, depending
on what you want you may want the parts in separated cells, when you posted your same
response earlier. If you can't figure it out you will have to be more specific about what
have in Column A and what you want in Column B as the answer or in Cols B & C as the answers.

is it possible for me to enter mulptiple formulas in one column?
somedays i need people to work 8 hours cash and stock and for them to work
cash for the first 3 hours and 4 hours stock after. Certain days i need them
to work to work stock for the first 4 hours and 3 hours cash after.

if column A is 3cash 4stock 4stock 4cash 6cash
5stock
(cash hrs)B would = 3 4 8
0
(stockhrs)C would = 4 4 6
5

i don't think this can be done, i have 8 differrent depts and i want each
dept column to extract their hours. like cash would extract its hours from
column A if there is a cash shift in it, like the example above.

On Sat, 4 Feb 2006 21:28:26 -0800, "cj" wrote:

thanks guys for the answer!

all 3 differrent formulas worked! i also need cell a2 to extract value of 8
if a1 is just "cash", value of 7 if a1 is "7cash",value of 6 if a1 is
"6cash", and so on and same for a3 to extract 8 if a1 is just "stock", etc.
and what if the order is reveresed from 3cash 4stock to 4stock 3cash?

plz help



You seem to be gradually adding conditions to your original request. It would
be best if you listed your full specifications initially.

Here is a method to extract a whole (integer) number preceding the particular
word.

In addition, there can be zero or several spaces between the number and the
word.

Also, the function is case insensitive.

1. Download and install Longre's free morefunc.xll add-in from

2.

a2: =REGEX.MID(A1,"\d+(?=\s*cash)",,FALSE)
a3: =REGEX.MID(A1,"\d+(?=\s*stock)",,FALSE)


--ron

thanks guys both formulas work great. is there any way i can add in
"inventory" to the stock formula and "office" to the cash formula?

"Ron Rosenfeld" wrote:

On Sat, 4 Feb 2006 21:28:26 -0800, "cj" wrote:

thanks guys for the answer!

all 3 differrent formulas worked! i also need cell a2 to extract value of 8
if a1 is just "cash", value of 7 if a1 is "7cash",value of 6 if a1 is
"6cash", and so on and same for a3 to extract 8 if a1 is just "stock", etc.
and what if the order is reveresed from 3cash 4stock to 4stock 3cash?

plz help



You seem to be gradually adding conditions to your original request. It would
be best if you listed your full specifications initially.

Here is a method to extract a whole (integer) number preceding the particular
word.

In addition, there can be zero or several spaces between the number and the
word.

Also, the function is case insensitive.

1. Download and install Longre's free morefunc.xll add-in from

2.

a2: =REGEX.MID(A1,"\d+(?=\s*cash)",,FALSE)
a3: =REGEX.MID(A1,"\d+(?=\s*stock)",,FALSE)


--ron

On Sun, 5 Feb 2006 14:19:27 -0800, "cj" wrote:

thanks guys both formulas work great. is there any way i can add in
"inventory" to the stock formula and "office" to the cash formula?

Yes there is.

But I don't know what you mean.

A2: =REGEX.MID(A1,"\d+(?=\s*cash)",,FALSE) & " office"
A3: =REGEX.MID(A1,"\d+(?=\s*stock)",,FALSE) & " inventory"

?????

--ron

hi Ron

on some days i want to replace cash with office in a1, and a2 still extract
the numbers whether it's 6cash or 6office.

"Ron Rosenfeld" wrote:

On Sun, 5 Feb 2006 14:19:27 -0800, "cj" wrote:

thanks guys both formulas work great. is there any way i can add in
"inventory" to the stock formula and "office" to the cash formula?

Yes there is.

But I don't know what you mean.

A2: =REGEX.MID(A1,"\d+(?=\s*cash)",,FALSE) & " office"
A3: =REGEX.MID(A1,"\d+(?=\s*stock)",,FALSE) & " inventory"

?????

--ron

On Sun, 5 Feb 2006 18:16:27 -0800, "cj" wrote:

hi Ron

on some days i want to replace cash with office in a1, and a2 still extract
the numbers whether it's 6cash or 6office.


Ah, one of the nice things about regular expressions is that this sort of
modification is easy:

A2: =REGEX.MID(A1,"\d+(?=\s*(cash|office))",,FALSE)
A3: =REGEX.MID(A1,"\d+(?=\s*(stock|inventory))",,FALSE )

Note the portion of the phrase (cash|office)

The pipe | means use either cash or office.
That they are enclosed in parenthesis properly groups them together.

The first function will now extract a number that is followed by zero or more
spaces and then either the word cash or the word office.


--ron

thanks ron for your answers

how do i expand the formula to extract and add numbers in different
combinations and order?
eg:
a1: "3cash" "4stock" "5train" "3cash 4stock" "4train 3cash 1stock"

a2: 3 4 5 7
8


"Ron Rosenfeld" wrote:

On Sun, 5 Feb 2006 18:16:27 -0800, "cj" wrote:

hi Ron

on some days i want to replace cash with office in a1, and a2 still extract
the numbers whether it's 6cash or 6office.


Ah, one of the nice things about regular expressions is that this sort of
modification is easy:

A2: =REGEX.MID(A1,"\d+(?=\s*(cash|office))",,FALSE)
A3: =REGEX.MID(A1,"\d+(?=\s*(stock|inventory))",,FALSE )

Note the portion of the phrase (cash|office)

The pipe | means use either cash or office.
That they are enclosed in parenthesis properly groups them together.

The first function will now extract a number that is followed by zero or more
spaces and then either the word cash or the word office.


--ron

On Sun, 5 Feb 2006 19:26:28 -0800, "cj" wrote:

thanks ron for your answers

how do i expand the formula to extract and add numbers in different
combinations and order?
eg:
a1: "3cash" "4stock" "5train" "3cash 4stock" "4train 3cash 1stock"

a2: 3 4 5 7
8

Try this:

=EVAL(REGEX.SUBSTITUTE(A1&"0","(\D+)","+"))

First we add a zero to the end of the string. You'll see why in a minute.

Now your phrases consist of digits followed by characters that are NOT digits.
The regex "{\D+)" says replace sets of characters that are not Digits with a
"+". The string of "not digits" can be 1 to many characters long.

So with "4train 3cash 1stock", after the substitutions, we have 4+3+1+0.

The EVAL function is also from morefunc.xll. I've not used it before but it
"Evaluates a formula or expression that is in the form of text and returns the
result."

So we've constructed the text and then apply the EVAL function to get the
result you want.

Let me know if it works out OK.


--ron

it didn't work, it can back with #value error.

=IF(ISERROR(SEARCH("cash",a1)),0,IF(SEARCH("cash", a1)=1,8,IF(AND(SEARCH("cash",a1)=2,LEN(a1)<=6),LEF T(a1,1),MID(a1,SEARCH("cash",a1,1)-1,1))))

is there any way i can add to the above formula to extract numbers from cash
or office or stock?

"Ron Rosenfeld" wrote:

On Sun, 5 Feb 2006 19:26:28 -0800, "cj" wrote:

thanks ron for your answers

how do i expand the formula to extract and add numbers in different
combinations and order?
eg:
a1: "3cash" "4stock" "5train" "3cash 4stock" "4train 3cash 1stock"

a2: 3 4 5 7
8

Try this:

=EVAL(REGEX.SUBSTITUTE(A1&"0","(\D+)","+"))

First we add a zero to the end of the string. You'll see why in a minute.

Now your phrases consist of digits followed by characters that are NOT digits.
The regex "{\D+)" says replace sets of characters that are not Digits with a
"+". The string of "not digits" can be 1 to many characters long.

So with "4train 3cash 1stock", after the substitutions, we have 4+3+1+0.

The EVAL function is also from morefunc.xll. I've not used it before but it
"Evaluates a formula or expression that is in the form of text and returns the
result."

So we've constructed the text and then apply the EVAL function to get the
result you want.

Let me know if it works out OK.


--ron

On Sun, 5 Feb 2006 21:23:27 -0800, "cj" wrote:

it didn't work, it can back with #value error.

Then either the data is not as you've posted it, or you made a typo in the
formula.

Post back with an exact copy of the data you used, and an exact copy of the
formula you used.


--ron

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?


"Ron Rosenfeld" wrote:

On Sun, 5 Feb 2006 21:23:27 -0800, "cj" wrote:

it didn't work, it can back with #value error.

Then either the data is not as you've posted it, or you made a typo in the
formula.

Post back with an exact copy of the data you used, and an exact copy of the
formula you used.


--ron

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