IF a Cell Contains Text in Google Sheets (Partial Match)

To return something when a cell contains a word, not when it equals it, IF needs help from a function that searches inside text. The first thing most people try, =IF(E2="*late*", …), doesn’t work in Google Sheets. Below are three versions that do, and what each did with ten real comments.

Column E holds teachers’ comments on ten assignments. We want “Late” next to every comment that mentions lateness:

Row E: Comment
2, 11 On time
3 Late submission
4, 10 (empty)
5 late
6 Excused (LATE start)
7 Plate design
8 Resubmitted
9 Re-take

Rows 3, 5 and 6 mention lateness, in three different cases. Row 7 is a trap: “Plate” contains the letters “late”.

Tested in Google Sheets. Every formula on this page was run on November 13, 2026, and the results below are the ones Google Sheets returned. How we test

Three formulas that work

=IF(REGEXMATCH(E2, "(?i)late"), "Late", "")
=IF(ISNUMBER(SEARCH("late", E2)), "Late", "")
=IF(COUNTIF(E2, "*late*"), "Late", "")

Copied down to row 11, all three marked the same four rows: 3, 5, 6 and 7. They found “Late”, “late” and “LATE”, and they also caught “Plate”. The next sections fix that.

  • REGEXMATCH returns TRUE or FALSE, which is exactly what IF needs. (?i) makes it ignore case; without it, REGEXMATCH is case-sensitive.
  • SEARCH returns the position of the word, or an error when it isn’t there. ISNUMBER turns that into TRUE or FALSE. SEARCH ignores case.
  • COUNTIF on a single cell returns 1 or 0, and IF treats 1 as true and 0 as false. It’s the only one of the three that understands the * wildcard. Our COUNTIF contains text guide covers wildcards in detail.

Why “*late*” doesn’t work in IF

=IF(E2="*late*", "Late", "No") returned No on every row, with no error. The equals sign compares the cell with the literal text “*late*”, asterisks included. Wildcards only work inside functions that support them, such as COUNTIF, SUMIF and MATCH.

SEARCH without ISNUMBER returns #VALUE!

=IF(SEARCH("late", E2), "Late", "No") worked on the rows that contain “late”, but every other row showed #VALUE! with the message In SEARCH evaluation, cannot find ‘late’ within ‘On time’. The empty rows gave the same error. IF never reached its “No” because SEARCH failed first. Wrapping SEARCH in ISNUMBER, as above, turns the error into FALSE.

Whole words only

To skip “Plate”, ask for “late” as a separate word. In a regular expression, \b marks a word boundary:

=IF(REGEXMATCH(E2, "(?i)\blate\b"), "Late", "")

This marked rows 3, 5 and 6, and left out row 7.

Matching the case exactly

FIND works like SEARCH but is case-sensitive:

=IF(ISNUMBER(FIND("late", E2)), "Late", "")

It marked only row 5 (“late”) and row 7 (“Plate”). It skipped “Late submission” and “LATE start”. Use it only when the case really carries meaning, such as a product code.

Contains one word or another

To flag comments that mention lateness or an excuse:

=IF(REGEXMATCH(E2, "(?i)late|excused"), "Flag", "")
=IF(OR(ISNUMBER(SEARCH("late", E2)), ISNUMBER(SEARCH("excused", E2))), "Flag", "")

Both flagged rows 3, 5, 6 and 7. In a regular expression, | means “or”. Row 6 contains both words and was flagged once, as it should be. For “contains both words”, put the two tests inside AND instead of OR.

For a whole column at once

Instead of copying the formula down, one formula can fill the column:

=ARRAYFORMULA(IF(REGEXMATCH(E2:E11, "(?i)late"), "Late", ""))

This returned the same four marks. The SEARCH version without ISNUMBER fails here too: =ARRAYFORMULA(IF(SEARCH("late", E2:E11), "Late", "")) filled every non-matching row with #VALUE!. More about one-formula columns is in ARRAYFORMULA with IF.

Related guides