When you allow users (or automated forms) to input data into your Google Sheets, the resulting dataset is often plagued with typos, incorrect formatting, or invalid information. A user might type a phone number with letters, or enter an email address missing the “@” symbol.
While basic Data Validation rules can catch simple errors, they fail when faced with complex formatting requirements (like ensuring a product code strictly follows a specific alphanumeric sequence). To enforce rigorous text formatting rules, you must use the REGEXMATCH function.
In this guide, you will learn how to use Regular Expressions (Regex) in Google Sheets to instantly validate text.
What is REGEXMATCH?
The REGEXMATCH function compares a string of text against a custom “Regular Expression” pattern. It outputs a simple TRUE or FALSE.
=REGEXMATCH(text, regular_expression)- text: The cell containing the data you want to check.
- regular_expression: The strict pattern rule (written in RE2 syntax) enclosed in quotation marks.
Use Case 1: Validating an Email Address
Suppose Column A contains a list of customer email addresses. You want to quickly highlight any entries that are obviously malformed (e.g., missing the @ symbol or the domain extension).
In Column B, you can use a Regex pattern designed specifically to identify the structure of an email:
=REGEXMATCH(A2, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$")If A2 contains [email protected], the formula returns TRUE. If it contains john.smithcompany.com, it returns FALSE, instantly flagging the bad data.
Use Case 2: Forcing a Specific Product Code Format
Imagine your company issues hardware with strict serial numbers. Every serial number must start with two uppercase letters, followed by a hyphen, and end with four numbers (e.g., AB-1234).
You can write a Regex pattern to enforce this exact sequence:
=REGEXMATCH(A2, "^[A-Z]{2}-[0-9]{4}$")- ^ means the pattern must start exactly here.
- [A-Z]{2} means look for exactly two uppercase letters from A to Z.
- – means look for a literal hyphen.
- [0-9]{4} means look for exactly four digits from 0 to 9.
- $ means the pattern must end exactly here.
If a user types ab-1234, the formula returns FALSE because the letters are lowercase. If they type AB-12345, it returns FALSE because there are five numbers instead of four.
Use Case 3: Combining with Data Validation
Returning TRUE or FALSE in an adjacent column is helpful, but the true power of REGEXMATCH is using it to actively block users from typing bad data in the first place.
- Highlight the cells you want to protect (e.g., A2:A100).
- Click Data > Data validation from the top menu.
- Add a rule. Under “Criteria”, select Custom formula is.
- Enter your formula, referencing the first cell in the range:
=REGEXMATCH(A2, "^[A-Z]{2}-[0-9]{4}$"). - Under “If the data is invalid”, select Reject input.
Now, if someone attempts to type an invalid serial number directly into the sheet, Google Sheets will throw an error popup and delete their input, ensuring your database remains perfectly pristine.