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.

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.

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.

can you post more examples?

Do you want to fetch only numeric data? or is the sentence too long "10 weak tigers........." etc?

All the best

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.

.

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.

.

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.

.

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.

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.

.