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

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

"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

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

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.

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



"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

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