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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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. . |
#5
|
|||
|
|||
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. . |
#6
|
|||
|
|||
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. . |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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. . |
Thread Tools | |
Display Modes | |
|
|