If part of a cell is true

[Eric D]

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?

 

[PCLIVE]

Probably several ways, but here's one possible way:

=IF(AND(ISNUMBER(FIND("ERIC",A1)),ISNUMBER(FIND("W-SETT",A1))),TRUE)

HTH,
Paul

 

[Mike]

Will "ERIC" and "W-SETT" always be the same

 

[David Biddulph]

=IF(AND(LEFT(B1,4)="ERIC",RIGHT(B1,6)="W-SETT"),"what you want for
TRUE","what you want for FALSE")

 

[Rick Rothstein \(MVP - VB\)]

Give this a try this...

=IF(ISNUMBER(--SUBSTITUTE(SUBSTITUTE(A1,"ERIC ",""),
" W-SETT","")),"Valid","Not Valid")

Rick

 

[Kevin B]

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.

 

[Billy Liddel]

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

 

[Dave Peterson]

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\)]

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

 

[Eric D]

Yes
--
Eric

Will "ERIC" and "W-SETT" always be the same

 

[Eric D]

Thank you this works great
--
Eric

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.

 

[Rick Rothstein \(MVP - VB\)]

Are you sure? Try it with something like this...

ERIC 12AB34 W-SETT

Rick

 

[Eric D]

it will never have letters in the middle like that, so this one works for
what i need

thanks for the concern though

 

[Rick Rothstein \(MVP - VB\)]

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