Conditional formula with text and number in cell
in [Functions]
|
From: Sheeloo on 30 May 2010 23:27 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 <RichM(a)discussions.microsoft.com> > > 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. > > > > . > >
From: RichM on 31 May 2010 07:22 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.
From: Steve Dunn on 1 Jun 2010 10:22
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" <Sheeloo(a)discussions.microsoft.com> wrote in message news:6D81A00B-8D88-48D3-81DB-A646A25C97B6(a)microsoft.com... > 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 >> > <RichM(a)discussions.microsoft.com> >> > 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. >> > >> > . >> > |