software

i need a formula to extract numbers.

if cell a1 is quot;3cash 5stockquot;, i want to extract the number quot;3quot; to cell a2
and extract the number quot;5quot; 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: www.mvps.org/dmcritchie/excel/excel.htm
Search Page: www.mvps.org/dmcritchie/excel/search.htm

quot;cjquot; gt; wrote ...
gt; i need a formula to extract numbers.
gt;
gt; if cell a1 is quot;3cash 5stockquot;, i want to extract the number quot;3quot; to cell a2
gt; and extract the number quot;5quot; to cell a3. the number in cash stock can vary
gt; between 1 to 8 and vice versa.
Hi!

Enter this formula in A2:

=IF(ISERROR(LEFT(A1)*1),quot;quot;,LEFT(A1)*1)

Enter this formula in A3:

=IF(ISERROR(MID(A1,FIND(quot; quot;,A1) 1,1)*1),quot;quot;,MID(A1,FIND(quot; quot;,A1) 1,1)*1)

Biff

quot;cjquot; gt; wrote in message
...
gt;i need a formula to extract numbers.
gt;
gt; if cell a1 is quot;3cash 5stockquot;, i want to extract the number quot;3quot; to cell a2
gt; and extract the number quot;5quot; to cell a3. the number in cash stock can vary
gt; between 1 to 8 and vice versa.
gt;
gt; can some help
On Sat, 4 Feb 2006 19:58:21 -0800, quot;cjquot; gt; wrote:

gt;i need a formula to extract numbers.
gt;
gt;if cell a1 is quot;3cash 5stockquot;, i want to extract the number quot;3quot; to cell a2
gt;and extract the number quot;5quot; to cell a3. the number in cash stock can vary
gt;between 1 to 8 and vice versa.
gt;
gt;can some help

If the values are always in that format, then:

a2:=LEFT(A1,FIND(quot;cashquot;,A1)-1)
A3:=MID(A1,FIND(quot;cashquot;,A1) 5,FIND(quot;stockquot;,A1)-FIND(quot;cashquot;,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 quot;cashquot;, value of 7 if a1 is quot;7cashquot;,value of 6 if a1 is
quot;6cashquot;, and so on and same for a3 to extract 8 if a1 is just quot;stockquot;, etc.
and what if the order is reveresed from 3cash 4stock to 4stock 3cash?

plz help

quot;Biffquot; wrote:

gt; Hi!
gt;
gt; Enter this formula in A2:
gt;
gt; =IF(ISERROR(LEFT(A1)*1),quot;quot;,LEFT(A1)*1)
gt;
gt; Enter this formula in A3:
gt;
gt; =IF(ISERROR(MID(A1,FIND(quot; quot;,A1) 1,1)*1),quot;quot;,MID(A1,FIND(quot; quot;,A1) 1,1)*1)
gt;
gt; Biff
gt;
gt; quot;cjquot; gt; wrote in message
gt; ...
gt; gt;i need a formula to extract numbers.
gt; gt;
gt; gt; if cell a1 is quot;3cash 5stockquot;, i want to extract the number quot;3quot; to cell a2
gt; gt; and extract the number quot;5quot; to cell a3. the number in cash stock can vary
gt; gt; between 1 to 8 and vice versa.
gt; gt;
gt; gt; can some help
gt;
gt;
gt;

thanks guys for the answer!

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

plz help
quot;Ron Rosenfeldquot; wrote:

gt; On Sat, 4 Feb 2006 19:58:21 -0800, quot;cjquot; gt; wrote:
gt;
gt; gt;i need a formula to extract numbers.
gt; gt;
gt; gt;if cell a1 is quot;3cash 5stockquot;, i want to extract the number quot;3quot; to cell a2
gt; gt;and extract the number quot;5quot; to cell a3. the number in cash stock can vary
gt; gt;between 1 to 8 and vice versa.
gt; gt;
gt; gt;can some help
gt;
gt; If the values are always in that format, then:
gt;
gt; a2:=LEFT(A1,FIND(quot;cashquot;,A1)-1)
gt; A3:=MID(A1,FIND(quot;cashquot;,A1) 5,FIND(quot;stockquot;,A1)-FIND(quot;cashquot;,A1)-5)
gt;
gt;
gt; --ron
gt;

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 quot;hackedquot; 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

quot;cjquot; gt; wrote in message
...
gt; thanks guys for the answer!
gt;
gt; all 3 differrent formulas worked! i also need cell a2 to extract value of
gt; 8
gt; if a1 is just quot;cashquot;, value of 7 if a1 is quot;7cashquot;,value of 6 if a1 is
gt; quot;6cashquot;, and so on and same for a3 to extract 8 if a1 is just quot;stockquot;,
gt; etc.
gt; and what if the order is reveresed from 3cash 4stock to 4stock 3cash?
gt;
gt; plz help
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; Hi!
gt;gt;
gt;gt; Enter this formula in A2:
gt;gt;
gt;gt; =IF(ISERROR(LEFT(A1)*1),quot;quot;,LEFT(A1)*1)
gt;gt;
gt;gt; Enter this formula in A3:
gt;gt;
gt;gt; =IF(ISERROR(MID(A1,FIND(quot; quot;,A1) 1,1)*1),quot;quot;,MID(A1,FIND(quot; quot;,A1) 1,1)*1)
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;cjquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;i need a formula to extract numbers.
gt;gt; gt;
gt;gt; gt; if cell a1 is quot;3cash 5stockquot;, i want to extract the number quot;3quot; to cell
gt;gt; gt; a2
gt;gt; gt; and extract the number quot;5quot; to cell a3. the number in cash stock can
gt;gt; gt; vary
gt;gt; gt; between 1 to 8 and vice versa.
gt;gt; gt;
gt;gt; gt; can some help
gt;gt;
gt;gt;
gt;gt;
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 amp; C as the answers.


quot;David McRitchiequot; wrote:

gt; Did you look at Biff's answer which pointed you back to his original answer, depending
gt; on what you want you may want the parts in separated cells, when you posted your same
gt; response earlier. If you can't figure it out you will have to be more specific about what
gt; have in Column A and what you want in Column B as the answer or in Cols B amp; C as the answers.
gt;
gt; 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(quot;cashquot;,$A1)),0,IF(SEARCH(quot;cashquot; ,$A1)=1,8,IF(AND(SEARCH(quot;cashquot;,$A1)=2,LEN($A1)lt;6), LEFT($A1,1),MID($A1,SEARCH(quot;cashquot;,$A1,1)-1,1))))
and this in your stock column
=IF(ISERROR(SEARCH(quot;stockquot;,A1)),0,IF(SEARCH(quot;stock quot;,A1)=1,8,IF(AND(SEARCH(quot;stockquot;,A1)=2,LEN(A1)lt;=6), LEFT(A1,1),MID(A1,SEARCH(quot;stockquot;,A1,1)-1,1))))
--
paul
remove nospam for email addy!
quot;cjquot; wrote:

gt;
gt;
gt; quot;David McRitchiequot; wrote:
gt;
gt; gt; Did you look at Biff's answer which pointed you back to his original answer, depending
gt; gt; on what you want you may want the parts in separated cells, when you posted your same
gt; gt; response earlier. If you can't figure it out you will have to be more specific about what
gt; gt; have in Column A and what you want in Column B as the answer or in Cols B amp; C as the answers.
gt; gt;
gt; gt; is it possible for me to enter mulptiple formulas in one column?
gt; somedays i need people to work 8 hours cash and stock and for them to work
gt; cash for the first 3 hours and 4 hours stock after. Certain days i need them
gt; to work to work stock for the first 4 hours and 3 hours cash after.
gt;
gt; if column A is 3cash 4stock 4stock 4cash 6cash
gt; 5stock
gt; (cash hrs)B would = 3 4 8
gt; 0
gt; (stockhrs)C would = 4 4 6
gt; 5
gt;
gt; i don't think this can be done, i have 8 differrent depts and i want each
gt; dept column to extract their hours. like cash would extract its hours from
gt; column A if there is a cash shift in it, like the example above.