Search for text within a range of cells in Excel using an IF statement

0
votes

I need to search for text within a range/array of cells in Excel.

Right now I have a working formula to search for 2 non-printing characters in a single cell, but I need to search multiple cells on the same row.

What is working: =IF(NOT(ISERROR(SEARCH(" ",$H2))),"Non-printing character",IF(NOT(ISERROR(SEARCH(" ",$H2))),"Non-printing character","OK"))

What I want to do is search $H2:$Q2 and if a non-printing character is contained anywhere within $H2:$Q2, I want cell G2 to display "Non-printing character." If a non-printing character is not found, I want G2 to display "OK."

Any ideas?

2
excelexcel-formula
Turns out I don't need a nested statement. – A Researcher

2 Answers

1
votes

This is an extension of your posted formula to test multiple cells:

=IF(NOT(ISERROR(SEARCH(" ",TEXTJOIN("",TRUE,H2:Q2)))),"Non-printing character",IF(NOT(ISERROR(SEARCH(" ",TEXTJOIN("",TRUE,H2:Q2)))),"Non-printing character","OK"))

We replaced:

$H2

with:

TEXTJOIN("",TRUE,H2:Q2)
0
votes

Since I don't need to nest, and I just need to find one non-printing character within a range, I was able to use the following formula:

=IF(NOT(ISERROR(SEARCH(" ",CONCATENATE($H2,$I2,$J2,$K2,$L2,$M2,$N2,$O2,$P2,$Q2)))),"Non-printing character","OK")