Spreadsheet Practice

How to use REGEXMATCH with IF in Google Sheets

Google Sheets formula

=IF(REGEXMATCH(A2,"^\d{5}(-\d{4})?$"),"Valid","Check")

REGEXMATCH returns TRUE or FALSE, so it slots straight into IF: =IF(REGEXMATCH(A2,"^\d{5}(-\d{4})?$"),"Valid","Check").

Result on the example
one result per row in B2, filled down to B8
Excel
See below

See it work

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

How it works

REGEXMATCH returns TRUE when the text matches the pattern. ^ and $ pin the match to the start and end, \d{5} means exactly five digits and (-\d{4})? allows an optional dash and four more. Without the anchors, "123456" would pass because it contains five digits. "554O1" has a letter O and fails.

=IF(REGEXMATCH(A2,"^\d{5}(-\d{4})?$"),"Valid","Check")

Result"Valid"

  1. A2"55401"

    The value in A2.

  2. REGEXMATCH(A2,"^\d{5}(-\d{4})?$")TRUE

    REGEXMATCH: TRUE if the text contains a match for the pattern.

    • textA2"55401"
    • regular_expression"^\d{5}(-\d{4})?$"
  3. IF(REGEXMATCH(A2,"^\d{5}(-\d{4})?$"),"Valid","Check")"Valid"

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

    • logical_testREGEXMATCH(A2,"^\d{5}(-\d{4})?$")TRUE
    • value_if_true"Valid"
    • value_if_false"Check"not used

In Excel

Excel for Microsoft 365 has REGEXTEST, which works the same way: =IF(REGEXTEST(A2,"^\d{5}(-\d{4})?$"),"Valid","Check"). In older versions, check the length and digits instead: =IF(AND(LEN(A2)=5,ISNUMBER(--A2)),"Valid","Check"), which accepts only the 5-digit form.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=IF(AND(LEN(A2)=5,ISNUMBER(--A2)),"Valid","Check")Valid, Check, Check…Different resultWithout regular expressions: five characters that convert to a number. Works in both apps. Rejects the ZIP+4 code 55401-1234.
=IF(REGEXTEST(A2,"^\d{5}(-\d{4})?$"),"Valid","Check")Excel onlyValid, Check, Valid…The Excel for Microsoft 365 equivalent.

Common mistakes

  • REGEXMATCH needs text. A ZIP code stored as a number returns an error; wrap it: REGEXMATCH(TO_TEXT(A2),…).
  • Matching is case-sensitive. Start the pattern with (?i) to ignore case.
  • ZIP codes stored as numbers lose their leading zero (02134 becomes 2134). Keep the column as plain text.

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 REGEXMATCH with IF

In B2, show "Valid" when the ZIP code is five digits, optionally followed by a dash and four digits, otherwise "Check". Fill down to B8.

Questions

How do I check an email format?

=REGEXMATCH(A2,"^[^@\s]+@[^@\s]+\.[^@\s]+$") checks for one @ with text on both sides and a dot after it.

How do I pull out the matching part instead?

Use REGEXEXTRACT: =REGEXEXTRACT(A2,"\d{5}").

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.