ExcelRoadMap.com

Excel REGEXTEST Function Tutorial

Version: Microsoft 365
Last update:
Tip: Use ⬆️ ⬇️ to turn the page up and down. We recommend browsing with a large screen for a better reading experience.

1. Introduction to the REGEXTEST Function

The REGEXTEST function in Excel is a tool for validating text against specific patterns. It's useful in data cleaning, validation of inputs (like emails, phone numbers, etc.), and extracting valuable insights from textual data. By integrating regular expressions, users can efficiently identify mismatches or validate formats in their data, enhancing overall data integrity.
Introduction to the REGEXTEST Function

2. Parameters of the REGEXTEST Function

Here is the syntax for using the REGEXTEST function.
=REGEXTEST(text, pattern, [case_sensitivity])
  • text: required The text or the reference to a cell containing the text you want to match against.
  • pattern: required The regular expression ("regex") that describes the pattern of text you want to match.
  • case_sensitivity: optional Determines whether the match is case-sensitive. By default, the match is case-sensitive. Enter one of the following:
  • 0: Case sensitive
  • 1: Case insensitive

    • 3. Example Scenario: Validate Email Address.

    Suppose we have collected some email addresses, and we want to know if these emails are valid email addresses.
    Example Scenario: Validate Email Address.Example Scenario: Validate Email Address.

    4. Getting Started with the REGEXTEST Function

    Type on the keyboard: =REGEXREPLACE(, then select A5 as the text to process.
    Getting Started with the REGEXTEST Function

    5. Regular Expression for Email

    Here we set the regular expression to validate emails as: [a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}.
    Regular Expression for Email

    6. Analysis of Email Regular Expression

    Here is the breakdown of the regular expression for email.
    Text:
    Regular Expression:
    [email protected][a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}
    1john.doe[a-zA-Z0-9._%+-]+
    2@@
    3company[a-zA-Z0-9.-]+
    4.\.
    5com[a-zA-Z]{2,}
    1[a-zA-Z0-9._%+-]+
    [a-zA-Z0-9._%+-] Character set
    a to z,   A to Z,   0 to 9,   character .,   character _,   character %,   character +,   character -.
    + Quantifier
    match 1 or more of the preceding token
    2@ Character
    matches a "@" character
    3[a-zA-Z0-9.-]+
    [a-zA-Z0-9.-] Character set
    a to z,   A to Z,   0 to 9,   character .,   character -.
    + Quantifier
    match 1 or more of the preceding token
    4\. Character
    matches a "." character
    5[a-zA-Z]{2,}
    [a-zA-Z] Character set
    a to z,   A to Z.
    {2,} Quantifier
    match 2 or more of the preceding token

    7. REGEXREPLACE Function Activation

    After hitting enter to save, we can see whether the email address matches the rules of the regular expression.

    If it matches, it returns TRUE; if not, it returns FALSE.
    REGEXREPLACE Function Activation

    8. Apply to Other Rows

    By applying the same function to other rows, we can quickly validate all email addresses.
    Apply to Other Rows

    9. 🎉 Finish! 🎉

    Author's Note: I hope you can feel the effort I put into these tutorials. I hope to create a series of very easy-to-understand Excel tutorials.

    If it is useful, help me share these tutorials, thank you!


    Follow me:

    Related Tutorials

  • LET Tutorial
  • PERCENTOF Tutorial
  • REGEXEXTRACT Tutorial
  • REGEXREPLACE Tutorial
  • REGEXTEST Tutorial
  • TEXTSPLIT Tutorial
  • TEXTJOIN Tutorial
  • VALUETOTEXT Tutorial
  • CHOOSECOLS Tutorial
  • CHOOSEROWS Tutorial
  • WRAPCOLS Tutorial
  • WRAPROWS Tutorial
  • DROP Tutorial
  • SCAN Tutorial
  • SMALL Tutorial
  • BYCOL Tutorial
  • BYROW Tutorial
    • Introduction to the REGEXTEST Function