How to Check for Text with ISTEXT in Excel

Use ISTEXT to find notes like "ABS" hiding in a column of numbers and handle them separately.

You will learn to

• Test whether a cell contains text
• Return a note or a calculation depending on type
• Work out what share of a column is text

How it works

ISTEXT(value) returns TRUE when the value is text and FALSE for numbers, dates, TRUE/FALSE, errors and empty cells. It is the natural check for columns that mix numbers with notes, such as exam marks where some entries say "ABS".

Use it inside IF to treat the two kinds of entry differently — =IF(ISTEXT(B2),"No mark: "&B2,B2/50) — or inside SUMPRODUCT to count how many entries are text: =SUMPRODUCT(--ISTEXT(B2:B9)).

=IF(ISTEXT(B2),"No mark: "&B2,B2/50)