Check if any Cell in a Range Contains Text – Excel & Google Sheets

This tutorial will demonstrate how to check if any cell in a range contains any text in Excel and Google Sheets.

 

Check if Range Contains Any Text

To test if any cell in a range contains any text, we will use the ISTEXT and SUMPRODUCT Functions.

ISTEXT Function

The ISTEXT Function does exactly what its name implies. It tests if a cell is text, outputting TRUE or FALSE.

=ISTEXT(A2)

SUMPRODUCT Function

The ISTEXT Function only tests a single cell, so we must find a way to test the entire range. This is where the SUMPRODUCT Function comes in. The SUMPRODUCT Function sums together arrays of values. So if we test each cell individually, outputting a 1 (TRUE) or 0 (FALSE) if each cell as text, then we can sum the values together. If the sum is greater than 0 than we know at least one cell contained text.

First, here is the final calculation:

=SUMPRODUCT(--ISTEXT(A2:C2))>0

Now let’s walk through it.

=SUMPRODUCT(--ISTEXT(A2:C2))>0
=SUMPRODUCT(--{FALSE TRUE, TRUE})>0
=SUMPRODUCT({0, 1, 1})>0
=2>0
=TRUE

Note: the double negatives converts the TRUE/FALSE into 1 / 0. Alternatively you could multiply the ISTEXT Function by *1

=SUMPRODUCT(ISTEXT(A2:C2)*1)>0

Instead of outputting TRUE / FALSE, you can use an IF Statement to output other text or calculations:

=IF(SUMPRODUCT(--ISTEXT(A2:C2))>0, "Yes", "No")

Google Sheets – Check if any Cell in a Range Contains Text

All of the above examples work exactly the same in Google Sheets as in Excel.