REGEXMATCH

Tests if some text matches a regular expression. It returns an array of matched text or a boolean value: TRUE if the text matches the regular expression, FALSE otherwise.

Syntax:

REGEXMATCH(text, regular_expression, full, case_type)

text is the string that you want a regular expression to match.

regular_expression is the regular expression that describes the pattern that the text matches.

full is optional, and it determines whether to return an array or a boolean TRUE/FALSE. By default, returns TRUE/FALSE.

0: returns TRUE/FALSE

1: returns an array with all results matching the complete regular expression

case_type is optional, and it determines whether the matching operation ignores the case. By default, the match is case-sensitive.

0: case-sensitive

1: case-insensitive

Example: 

text: The only way to do great work is to love what you do.

regular_expression: [a-z]+

returns TRUE

full: 1

returns ["he","only","way","to","do","great","work","is","to","love","what","you","do"]

full: 1 and case_type: 1

returns ["The","only","way","to","do","great","work","is","to","love","what","you","do"]

Here's a breakdown of the usage and the results in the table:

The Formula: REGEXMATCH(B1, "^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$")

• REGEXMATCH: This is the function itself.
• B1: This is the first argument, which is a cell reference. It tells the function to look at the content of cell B2, which contains the email address "john.doe@email.com".
• "^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$": This is the second argument, the regular expression pattern. It is a string that defines the rules for a valid email address.

Breakdown of the Regular Expression:

• ^: This symbol anchors the match to the beginning of the string. The email must start with the pattern that follows.
• [A-Za-z0-9._%+-]+: This is the "username" part of the email.
• • []: A character set. It means any character inside these brackets is a match.
  • A-Za-z0-9: Matches any uppercase letter, lowercase letter, or digit.
  • ._%+-: Matches the literal characters period, underscore, percent, plus, or hyphen.
  • +: A quantifier that means "one or more" of the preceding characters.
• @: This matches the literal "at" symbol. It is not a special character in regex and must be present exactly once.
• [A-Za-z0-9.-]+: This is the "domain name" part. It's similar to the username, allowing letters, digits, periods, and hyphens.
• \.: Matches a literal period. The backslash \ is an escape character that tells the regex engine to treat the period as a period, not as a special "any character" wildcard.
• [A-Za-z]{2,}: This is the "top-level domain" (e.g., "com", "net").
• • []: Again, a character set. This one only allows letters.
  • {2,}: A quantifier that means "two or more" of the preceding characters.
• $: This symbol anchors the match to the end of the string. The email must end with the pattern that precedes it.

The Results

The REGEXMATCH function processes each cell in the "Email Address" column and produces a result in the "Is Valid?" column.

• TRUE: The function returns TRUE for the email addresses "john.doe@email.com", "jane_smith@work-email.net", and "robert.brown@company.co.uk". This is because they all conform to the specified pattern. They have a valid username part, an "@" symbol, a domain, a period, and a top-level domain with at least two letters.
• FALSE: The function returns FALSE for "peter@jones" and "mary.johnson@.com".
• • "peter@jones" is invalid because it lacks a period and a top-level domain. The regex pattern requires both.
  • "mary.johnson@.com" is invalid because the domain name part is empty. The [A-Za-z0-9.-]+ part requires at least one character.