Conditional formula with text and number in cell

  • Thread starter Thread starter RichM
  • Start date Start date
R

RichM

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.
 
=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
 
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...
 
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.
 
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.
 
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)))
 
Thanks very much

Sheeloo said:
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...
Click to expand...
 
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")
 
Back
Top