Conditional formula with text and number in cell

[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.

 

[Gord Dibben]

=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

 

[Sheeloo]

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]

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.

 

[RichM]

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.

 

[Sheeloo]

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]

Thanks very much

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...

 

[Steve Dunn]

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")