Find if any of the cells in a row contain a text string.

[artins]

Hi, I have a simple question (or at least it seams but I can't get it working).

I need to see if the cells from B2:BM2 contain the word "WARNING" and if yes return "WARNING" in A2 if not blank.

Please help

 

[T. Valko]

Hi, I have a simple question (or at least it seams but I can't get it working).

I need to see if the cells from B2:BM2 contain the word "WARNING" and if yes return "WARNING" in A2 if not blank.

Please help

Assuming you mean that if ANY cell contains WARNING then return the same.

One way...

=IF(COUNTIF(B2:BM2,"warning"),"WARNING","")

 

[Aladin Akyurek]

Hi, I have a simple question (or at least it seams but I can't get it working).

I need to see if the cells from B2:BM2 contain the word "WARNING" and if yes return "WARNING" in A2 if not blank.

Please help

Try...

IF(COUNTIF(B2:BM2,"*WARNING*"),"WARNING","")

 

[artins]

Thank you so much. It worked!

 

[T. Valko]

Thank you so much. It worked!

Did you mean the cells might also contain other text including the word warning?

Like this:

B2 = 1st Warning

Or, did you mean the cells will contain only the word warning (if any do)?

Like this:

B2 = Warning

 

[artins]

For this project was the word "Warning" in capital letters only.

But for the sake of expanding my excel knowledge How would you rewrite this formula for all cases (not case sensitive) + cells including the string.

Thanks

 

[T. Valko]

For this project was the word "Warning" in capital letters only.

But for the sake of expanding my excel knowledge How would you rewrite this formula for all cases (not case sensitive) + cells including the string.

Thanks

Neither of the formulas suggested are case sensitive.

The reason I asked for clarification is the other poster used a different criteria and I just wanted to verify my understanding of what was asked.

If you want something that is case sensitive...

=IF(SUMPRODUCT(--(EXACT(B2:BM2,"WARNING"))),"WARNING","")

If at least one entry in the range matches the word WARNING exactly then the formula returns the same.

 

[artins]

thanks a bunch

 

[T. Valko]

thanks a bunch

You're welcome!