Check if cell contains part of a string

[sharky12345]

I have a cell which has a formula result in it which at the moment is like this;

"E0700-1500"

That cell can have any one of a number of different values in different formats, but I'm trying to find a formula that can tell me if the cell value has a hyphen in it.

I thought doing something like *-* would do it but it doesn't so does anyone have an idea?

 

[Joe4]

Try:

=ISNUMBER(FIND("-",A1))

 

[sharky12345]

Joe4,

Thanks - works a treat!

Can I add text though?

So if the result is TRUE I need "Available" and if False "Unavailable"

Thanks in advance and sorry, formulas are not my forte!

 

[sharky12345]

Disregard - had a play and worked it out!

=IF(ISNUMBER(FIND("-",J4)),"Available","Unavailable")

 

[Joe4]

Sure, just wrap it in an IF statement. Since the formula already returns TRUE/FALSE, that is really easy:

=[COLOR=#ff0000]IF([/COLOR][COLOR=#0000ff]ISNUMBER(FIND("-",A1))[/COLOR][COLOR=#333333],[/COLOR][COLOR=#ff0000]"Available","Unavailable")[/COLOR]

 

[sharky12345]

Thanks Joe4,

My interest has been sparked!

So how about if I wanted to combine ISNUMBER and ISTEXT? I've tried this but it doesn't work;

=IF(OR(ISNUMBER(FIND("-",J5)),ISTEXT(J5="NTE")),"Available","Unavailable")

 

[Joe4]

You wouldn't use ISTEXT. You have misunderstood how it works. Note that "-" is just as much of a string as "NTE" is!

If you break it down, we are first using the FIND function to find where the hyphen resides in cell J5.
If it finds it, it returns the number of the position it resides in.
If it does not find it, it returns an error.
So, we then use ISNUMBER to see if that result is a number (if it is, then it means we found a a hyphen, if it is not, that means that we do not find one).

So, if you wanted to check to see if your cell contains a hyphen OR the text"NTE", you would use:

[COLOR=#333333]=IF(OR(ISNUMBER(FIND("-",J4)),[/COLOR][COLOR=#333333]ISNUMBER(FIND("NTE",J4)))[/COLOR][COLOR=#333333],"Available","Unavailable")[/COLOR]

 

[sharky12345]

Now I understand!

Thanks - everyday is a day at school!

 

[Joe4]

You are welcome!
Glad I was able to help.