| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If part of a cell is true
When using IF statements, is it possible to return a value if part of the
cell is true. For example, in B1 there is "ERIC 12345 W-SETT" I want it to recognize that "ERIC ##### W-SETT" is true. The numbers change from day to day, so i need it to just say as long as there are numbers there then its true. Can anyone help? -- Eric |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If part of a cell is true
Probably several ways, but here's one possible way:
=IF(AND(ISNUMBER(FIND("ERIC",A1)),ISNUMBER(FIND("W-SETT",A1))),TRUE) HTH, Paul -- "Eric D" wrote in message ... When using IF statements, is it possible to return a value if part of the cell is true. For example, in B1 there is "ERIC 12345 W-SETT" I want it to recognize that "ERIC ##### W-SETT" is true. The numbers change from day to day, so i need it to just say as long as there are numbers there then its true. Can anyone help? -- Eric |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If part of a cell is true
Will "ERIC" and "W-SETT" always be the same
"Eric D" wrote: When using IF statements, is it possible to return a value if part of the cell is true. For example, in B1 there is "ERIC 12345 W-SETT" I want it to recognize that "ERIC ##### W-SETT" is true. The numbers change from day to day, so i need it to just say as long as there are numbers there then its true. Can anyone help? -- Eric |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If part of a cell is true
=IF(AND(LEFT(B1,4)="ERIC",RIGHT(B1,6)="W-SETT"),"what you want for
TRUE","what you want for FALSE") -- David Biddulph "Eric D" wrote in message ... When using IF statements, is it possible to return a value if part of the cell is true. For example, in B1 there is "ERIC 12345 W-SETT" I want it to recognize that "ERIC ##### W-SETT" is true. The numbers change from day to day, so i need it to just say as long as there are numbers there then its true. Can anyone help? -- Eric |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If part of a cell is true
Give this a try this...
=IF(ISNUMBER(--SUBSTITUTE(SUBSTITUTE(A1,"ERIC ",""), " W-SETT","")),"Valid","Not Valid") Rick "Eric D" wrote in message ... When using IF statements, is it possible to return a value if part of the cell is true. For example, in B1 there is "ERIC 12345 W-SETT" I want it to recognize that "ERIC ##### W-SETT" is true. The numbers change from day to day, so i need it to just say as long as there are numbers there then its true. Can anyone help? -- Eric |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If part of a cell is true
The following function will work as long as it's ERIC:
=IF(AND(LEFT(A1,4)="ERIC",RIGHT(A1,6)="W-SETT"),"True",FALSE) If that's not the case it can still be done with a bit of formula work. -- Kevin Backmann "Eric D" wrote: When using IF statements, is it possible to return a value if part of the cell is true. For example, in B1 there is "ERIC 12345 W-SETT" I want it to recognize that "ERIC ##### W-SETT" is true. The numbers change from day to day, so i need it to just say as long as there are numbers there then its true. Can anyone help? -- Eric |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If part of a cell is true
Try
=IF(AND(LEFT(B1,4)="eric",RIGHT(B1,6)="W-sett"),TRUE,FALSE) If Eric & W-SETT are shown in a cell somewhere you can refer to the cell that they are in. If A1 contains Eric and A2 contains W_SETT then use the formula =IF(AND(LEFT(A1,4)="eric",RIGHT(A2,6)="W-sett"),TRUE,FALSE) Regards Peter "Eric D" wrote: When using IF statements, is it possible to return a value if part of the cell is true. For example, in B1 there is "ERIC 12345 W-SETT" I want it to recognize that "ERIC ##### W-SETT" is true. The numbers change from day to day, so i need it to just say as long as there are numbers there then its true. Can anyone help? -- Eric |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If part of a cell is true
Just to build on Rick's suggestion:
Maybe checking for ERIC_ and _W-SETT and the length would be useful: =IF(AND(LEN(A1)=17,COUNTIF(A1,"ERIC * W-SETT")0, ISNUMBER(-SUBSTITUTE(SUBSTITUTE(A1,"ERIC ","")," W-SETT",""))), "Valid","Not Valid") But this isn't full proof. ERIC 13E45 W-SETT would be valid, since 13E45 is a number (in scientific notation) to excel. But it's pretty close. (Drop the len(a1)=17 if the length of the numbers can vary.) "Rick Rothstein (MVP - VB)" wrote: Give this a try this... =IF(ISNUMBER(--SUBSTITUTE(SUBSTITUTE(A1,"ERIC ",""), " W-SETT","")),"Valid","Not Valid") Rick "Eric D" wrote in message ... When using IF statements, is it possible to return a value if part of the cell is true. For example, in B1 there is "ERIC 12345 W-SETT" I want it to recognize that "ERIC ##### W-SETT" is true. The numbers change from day to day, so i need it to just say as long as there are numbers there then its true. Can anyone help? -- Eric -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If part of a cell is true
Good catch on the 13E45... I'm usually the one picking up on that problem
and I completely missed it here :-( This formula should be "foolproof"... =IF(SUMPRODUCT(--ISNUMBER(--MID(SUBSTITUTE(SUBSTITUTE(A1,"ERIC ",""), " W-SETT",""),ROW($1:$999),1)))=LEN(A1)-12,"Valid","Not Valid") What it does is see if the number of digits in the text (after the two substitutions have been made) equals the number of characters in the text after the fixed text has been removed. Your length check can be added if required (but I got the distinct impression that the number of digits could vary). Rick "Dave Peterson" wrote in message ... Just to build on Rick's suggestion: Maybe checking for ERIC_ and _W-SETT and the length would be useful: =IF(AND(LEN(A1)=17,COUNTIF(A1,"ERIC * W-SETT")0, ISNUMBER(-SUBSTITUTE(SUBSTITUTE(A1,"ERIC ","")," W-SETT",""))), "Valid","Not Valid") But this isn't full proof. ERIC 13E45 W-SETT would be valid, since 13E45 is a number (in scientific notation) to excel. But it's pretty close. (Drop the len(a1)=17 if the length of the numbers can vary.) "Rick Rothstein (MVP - VB)" wrote: Give this a try this... =IF(ISNUMBER(--SUBSTITUTE(SUBSTITUTE(A1,"ERIC ",""), " W-SETT","")),"Valid","Not Valid") Rick "Eric D" wrote in message ... When using IF statements, is it possible to return a value if part of the cell is true. For example, in B1 there is "ERIC 12345 W-SETT" I want it to recognize that "ERIC ##### W-SETT" is true. The numbers change from day to day, so i need it to just say as long as there are numbers there then its true. Can anyone help? -- Eric -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If part of a cell is true
Yes
-- Eric "Mike" wrote: Will "ERIC" and "W-SETT" always be the same "Eric D" wrote: When using IF statements, is it possible to return a value if part of the cell is true. For example, in B1 there is "ERIC 12345 W-SETT" I want it to recognize that "ERIC ##### W-SETT" is true. The numbers change from day to day, so i need it to just say as long as there are numbers there then its true. Can anyone help? -- Eric |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If part of a cell is true
Thank you this works great
-- Eric "Kevin B" wrote: The following function will work as long as it's ERIC: =IF(AND(LEFT(A1,4)="ERIC",RIGHT(A1,6)="W-SETT"),"True",FALSE) If that's not the case it can still be done with a bit of formula work. -- Kevin Backmann "Eric D" wrote: When using IF statements, is it possible to return a value if part of the cell is true. For example, in B1 there is "ERIC 12345 W-SETT" I want it to recognize that "ERIC ##### W-SETT" is true. The numbers change from day to day, so i need it to just say as long as there are numbers there then its true. Can anyone help? -- Eric |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If part of a cell is true
Are you sure? Try it with something like this...
ERIC 12AB34 W-SETT Rick "Eric D" wrote in message ... Thank you this works great -- Eric "Kevin B" wrote: The following function will work as long as it's ERIC: =IF(AND(LEFT(A1,4)="ERIC",RIGHT(A1,6)="W-SETT"),"True",FALSE) If that's not the case it can still be done with a bit of formula work. -- Kevin Backmann "Eric D" wrote: When using IF statements, is it possible to return a value if part of the cell is true. For example, in B1 there is "ERIC 12345 W-SETT" I want it to recognize that "ERIC ##### W-SETT" is true. The numbers change from day to day, so i need it to just say as long as there are numbers there then its true. Can anyone help? -- Eric |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If part of a cell is true
it will never have letters in the middle like that, so this one works for
what i need thanks for the concern though -- Eric "Rick Rothstein (MVP - VB)" wrote: Are you sure? Try it with something like this... ERIC 12AB34 W-SETT Rick "Eric D" wrote in message ... Thank you this works great -- Eric "Kevin B" wrote: The following function will work as long as it's ERIC: =IF(AND(LEFT(A1,4)="ERIC",RIGHT(A1,6)="W-SETT"),"True",FALSE) If that's not the case it can still be done with a bit of formula work. -- Kevin Backmann "Eric D" wrote: When using IF statements, is it possible to return a value if part of the cell is true. For example, in B1 there is "ERIC 12345 W-SETT" I want it to recognize that "ERIC ##### W-SETT" is true. The numbers change from day to day, so i need it to just say as long as there are numbers there then its true. Can anyone help? -- Eric |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If part of a cell is true
I was led astray by the last part of this statement of yours...
"I want it to recognize that "ERIC ##### W-SETT" is true. The numbers change from day to day, so i need it to just say as long as there are numbers there then its true." So, I am guessing these numbers are generated by a formula rather than being typed in by an individual (who could mistype the number by mistake). If that is the case, you might want to consider this shorter formula which features 2 less function calls... =IF(COUNTIF(A1,"ERIC * W-SETT"),"Valid","Not Valid") or, if you only need TRUE or FALSE returned, this version of it... =COUNTIF(A1,"ERIC * W-SETT")0 Rick "Eric D" wrote in message ... it will never have letters in the middle like that, so this one works for what i need thanks for the concern though -- Eric "Rick Rothstein (MVP - VB)" wrote: Are you sure? Try it with something like this... ERIC 12AB34 W-SETT Rick "Eric D" wrote in message ... Thank you this works great -- Eric "Kevin B" wrote: The following function will work as long as it's ERIC: =IF(AND(LEFT(A1,4)="ERIC",RIGHT(A1,6)="W-SETT"),"True",FALSE) If that's not the case it can still be done with a bit of formula work. -- Kevin Backmann "Eric D" wrote: When using IF statements, is it possible to return a value if part of the cell is true. For example, in B1 there is "ERIC 12345 W-SETT" I want it to recognize that "ERIC ##### W-SETT" is true. The numbers change from day to day, so i need it to just say as long as there are numbers there then its true. Can anyone help? -- Eric |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Search for 2 true arguments and return true or false | Excel Discussion (Misc queries) | |||
| Function to return True/False if all are validated as True by ISNU | Excel Worksheet Functions | |||
| How do I stop Excel from changing the word true to TRUE? | Excel Discussion (Misc queries) | |||
| First part of "IF" function returns false even if true. ?? | Excel Worksheet Functions | |||
| Reverse false and combine with true true value | Excel Worksheet Functions | |||
Linear Mode
