Spreadsheet Practice

How to use ISTEXT in an IF formula in Excel

Formula (Excel and Google Sheets)

=IF(ISTEXT(B2),"Check qty",B2*C2)

Return a message for text and calculate otherwise: =IF(ISTEXT(B2),"Check qty",B2*C2).

Result on the example
one result per row in D2, filled down to D7
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in D2, filled down to D7. Change any input and the result updates, or edit the formula to experiment.

How it works

ISTEXT is TRUE when the cell holds text, like "TBC" or "backorder". IF returns the message for those rows instead of letting the multiplication fail with #VALUE!. An empty cell isn't text, so A-15 calculates as 0.

=IF(ISTEXT(B2),"Check qty",B2*C2)

Result54

  1. B212

    The value in B2.

  2. ISTEXT(B2)FALSE

    ISTEXT: TRUE if the value is text.

    • valueB212
  3. C2$4.50

    The value in C2.

  4. B2*C254

    The * operator multiplies.

  5. IF(ISTEXT(B2),"Check qty",B2*C2)54

    IF: Returns one value if a test is true, another if false.

    • logical_testISTEXT(B2)FALSE
    • value_if_true"Check qty"not used
    • value_if_falseB2*C254

In Google Sheets

The same formula works in Google Sheets: =IF(ISTEXT(B2),"Check qty",B2*C2). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=IF(ISNUMBER(B2),B2*C2,"Check qty")$54.00, Check qty, $61.25…Different resultCalculates only for real numbers, so empty quantities are flagged too. A-15, with no quantity, shows "Check qty" instead of 0.
=IFERROR(B2*C2,"Check qty")$54.00, Check qty, $61.25…Lets the multiplication fail on text and catches the error. Shorter, but it hides other problems too.

Common mistakes

  • A number stored as text ("12") is text to ISTEXT, so it is flagged. That is usually right: fix the entry.
  • A formula that returns "" counts as text for ISTEXT.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Use ISTEXT in an IF formula

In D2, show "Check qty" when the quantity is text, otherwise multiply quantity by unit price. Fill down to D7.

Questions

How do I check for one particular word instead of any text?

Use SEARCH: =IF(ISNUMBER(SEARCH("backorder",B2)),"On backorder",B2*C2).

How do I count text entries in a column?

=COUNTIF(B2:B7,"*") counts cells that contain text.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.