If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
extracting numbers
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 |
#2
|
|||
|
|||
extracting numbers
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. |
#3
|
|||
|
|||
extracting numbers
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 |
#4
|
|||
|
|||
extracting numbers
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 |
#5
|
|||
|
|||
extracting numbers
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 |
#6
|
|||
|
|||
extracting numbers
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 |
#7
|
|||
|
|||
extracting numbers
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 |
#8
|
|||
|
|||
extracting numbers
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. |
#9
|
|||
|
|||
extracting numbers
"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. |
#10
|
|||
|
|||
extracting numbers
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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Numbers stored as text causes problem with VLOOKUP | bpeltzer | Worksheet Functions | 0 | February 4th, 2006 08:07 PM |
How to make excel not round real numbers when making a histogram? | Leedawg | Charts and Charting | 1 | September 21st, 2005 07:36 PM |
Extracting numbers from a cell | morchard | General Discussion | 2 | June 30th, 2005 10:41 AM |
extracting numbers | Trevor | General Discussion | 2 | November 4th, 2004 03:12 AM |