Coutif and isnumber functions for text

[Dryver14]

Good afternoon,

I was looking at some answers given earlier and think both formulas could be dangerous given long sentences

=IF(A1="","",IF(ISNUMBER(SEARCH("Terminal",A1)),"Term",IF(ISNUMBER(SEARCH("Wire",A1)),"Wire","Other"))

the other formula was countif with *Terminal* as part of the equation,

Having played with both formulas they return the correct result if part of the word is there eg: Term

Is there a safe lookup for words or are these formulas based on searching one cell for a return value?

 

[texasalynn]

post a small sample of what you are wanting and the expected results

 

[Dryver14]

I'm just learning Texasalynn,

I often take away the question someone has posted and the answers given and see how it works, I just found these answers kind of "Volotile" but I can see the benefit if the question is with regard to 1 of a few responces in a cell


Regards,
Kev

 

[T. Valko]

I'm just learning Texasalynn,

I often take away the question someone has posted and the answers given and see how it works, I just found these answers kind of "Volotile" but I can see the benefit if the question is with regard to 1 of a few responces in a cell


Regards,
Kev

When doing these types of lookups there's a chance of getting "false positives".

It depends on the data and knowing what the data is used for.

The search string may be part of another word like this. You want to find the whole separate word "good".

A1: John Goodman did a fine job.

In this case you get a "false positive" because the formula found "good" but it was part of another word.

ISNUMBER(SEARCH("good",A1)) = TRUE

One way to make it somewhat more robust (but not completely) when doing whole word matching is to concatenate space characters to the search string and the string being searched.

A1: John Goodman did a fine job.

ISNUMBER(SEARCH(" good "," "&A1&" ")) = FALSE

However, this fails if there might be punctuation marks present.

A1: Ice cream is so good!

ISNUMBER(SEARCH(" good "," "&A1&" ")) = FALSE

 

[Dryver14]

Thanks for the explaination and workaround Biff,


much appreciated.

 

[T. Valko]

Thanks for the explaination and workaround Biff,


much appreciated.

You're welcome!