| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formula with text and number in cell
Hello,
Can a conditional formula be written to convert a cell with text and a number to a cellw ith just a number? I have a cell with the number 10 and the word "weak" in it. 10 weak Can a conditional formula convert this to just the number "10"? I did =If(b1= 10 weak,"10") but it does not work. Thank you. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional formula with text and number in cell
=IF(B1="10 weak",10,"not 10 weak")
BTW............10 weak what's? 10 weak kittens? 10 weak cups of tea? Gord Dibben MS Excel MVP On Sat, 29 May 2010 11:47:01 -0700, RichM wrote: Hello, Can a conditional formula be written to convert a cell with text and a number to a cellw ith just a number? I have a cell with the number 10 and the word "weak" in it. 10 weak Can a conditional formula convert this to just the number "10"? I did =If(b1= 10 weak,"10") but it does not work. Thank you. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional formula with text and number in cell
You can not use conditional formula to change values in a cell... you can use
it to format the cells meeting a condition.. Also you can not use a formula to change value in the same cell... It you just want to remove " weak" then you can use find and replace... "RichM" wrote: Hello, Can a conditional formula be written to convert a cell with text and a number to a cellw ith just a number? I have a cell with the number 10 and the word "weak" in it. 10 weak Can a conditional formula convert this to just the number "10"? I did =If(b1= 10 weak,"10") but it does not work. Thank you. |
|
#4
|
|||
|
|||
Quote:
Do you want to fetch only numeric data? or is the sentence too long "10 weak tigers........." etc? All the best
__________________
Thanks Bala |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional formula with text and number in cell
Thank you Gord. That did it. It's a 10-point Likert scale with 1=strong,
10=weak. Asking about belief in ability to solve science questions. Thanks again. "Gord Dibben" wrote: =IF(B1="10 weak",10,"not 10 weak") BTW............10 weak what's? 10 weak kittens? 10 weak cups of tea? Gord Dibben MS Excel MVP On Sat, 29 May 2010 11:47:01 -0700, RichM wrote: Hello, Can a conditional formula be written to convert a cell with text and a number to a cellw ith just a number? I have a cell with the number 10 and the word "weak" in it. 10 weak Can a conditional formula convert this to just the number "10"? I did =If(b1= 10 weak,"10") but it does not work. Thank you. . |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional formula with text and number in cell
Hi Gord,
I hope you don't mind, one more question. I want to put a string of these conditions together and did this: =IF(E2="5 okay/strong",5,"not 5 okay/strong",if(e2="10 strong",10,"not 10 strong",if(e2="1 weak",1,"not 1 weak"))) But it doesn't work. Can you tell me what would work? Thanks again for your help. "Gord Dibben" wrote: =IF(B1="10 weak",10,"not 10 weak") BTW............10 weak what's? 10 weak kittens? 10 weak cups of tea? Gord Dibben MS Excel MVP On Sat, 29 May 2010 11:47:01 -0700, RichM wrote: Hello, Can a conditional formula be written to convert a cell with text and a number to a cellw ith just a number? I have a cell with the number 10 and the word "weak" in it. 10 weak Can a conditional formula convert this to just the number "10"? I did =If(b1= 10 weak,"10") but it does not work. Thank you. . |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional formula with text and number in cell
Rich,
Try =IF(E2="5 okay/strong",5,IF(E2="10 strong",10,IF(E2="1 weak",1,"not 5, 10 or 1"))) Essentially you put another IF in the False condition of the previous IF... Excel 2003 has a limit of 7 nested IFs. The above is like the following (just to explain... not valid syntax) =IF(cond1, cond1 true, IF(cond2, cond2 true, IF(cond3, cond3 true, cond1 & cond2 & comd3 all false))) "RichM" wrote: Hi Gord, I hope you don't mind, one more question. I want to put a string of these conditions together and did this: =IF(E2="5 okay/strong",5,"not 5 okay/strong",if(e2="10 strong",10,"not 10 strong",if(e2="1 weak",1,"not 1 weak"))) But it doesn't work. Can you tell me what would work? Thanks again for your help. "Gord Dibben" wrote: =IF(B1="10 weak",10,"not 10 weak") BTW............10 weak what's? 10 weak kittens? 10 weak cups of tea? Gord Dibben MS Excel MVP On Sat, 29 May 2010 11:47:01 -0700, RichM wrote: Hello, Can a conditional formula be written to convert a cell with text and a number to a cellw ith just a number? I have a cell with the number 10 and the word "weak" in it. 10 weak Can a conditional formula convert this to just the number "10"? I did =If(b1= 10 weak,"10") but it does not work. Thank you. . |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional formula with text and number in cell
Thanks very much
"Sheeloo" wrote: You can not use conditional formula to change values in a cell... you can use it to format the cells meeting a condition.. Also you can not use a formula to change value in the same cell... It you just want to remove " weak" then you can use find and replace... "RichM" wrote: Hello, Can a conditional formula be written to convert a cell with text and a number to a cellw ith just a number? I have a cell with the number 10 and the word "weak" in it. 10 weak Can a conditional formula convert this to just the number "10"? I did =If(b1= 10 weak,"10") but it does not work. Thank you. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional formula with text and number in cell
An alternative that wouldn't be constrained by nested IFs.
=IF(OR(VALUE(LEFT(E2,FIND(" ",E2)))={5,10,1}), VALUE(LEFT(E2,FIND(" ",E2))),"Not 5, 10, or 1") "Sheeloo" wrote in message ... Rich, Try =IF(E2="5 okay/strong",5,IF(E2="10 strong",10,IF(E2="1 weak",1,"not 5, 10 or 1"))) Essentially you put another IF in the False condition of the previous IF... Excel 2003 has a limit of 7 nested IFs. The above is like the following (just to explain... not valid syntax) =IF(cond1, cond1 true, IF(cond2, cond2 true, IF(cond3, cond3 true, cond1 & cond2 & comd3 all false))) "RichM" wrote: Hi Gord, I hope you don't mind, one more question. I want to put a string of these conditions together and did this: =IF(E2="5 okay/strong",5,"not 5 okay/strong",if(e2="10 strong",10,"not 10 strong",if(e2="1 weak",1,"not 1 weak"))) But it doesn't work. Can you tell me what would work? Thanks again for your help. "Gord Dibben" wrote: =IF(B1="10 weak",10,"not 10 weak") BTW............10 weak what's? 10 weak kittens? 10 weak cups of tea? Gord Dibben MS Excel MVP On Sat, 29 May 2010 11:47:01 -0700, RichM wrote: Hello, Can a conditional formula be written to convert a cell with text and a number to a cellw ith just a number? I have a cell with the number 10 and the word "weak" in it. 10 weak Can a conditional formula convert this to just the number "10"? I did =If(b1= 10 weak,"10") but it does not work. Thank you. . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Test whether formula in cell is an expression or a number/text | Excel Worksheet Functions | |||
| How do I attach a number to a cell with text to use in a formula? | Excel Discussion (Misc queries) | |||
| Excel Formula, text & number in cell | Excel Discussion (Misc queries) | |||
| number and text in cell for formula | Excel Worksheet Functions | |||
| conditional formatting - problem with text cell value from formula | Excel Worksheet Functions | |||
Linear Mode
