by Svetlana Cheusheva, updated on
The tutorial shows how to do data validation in Excel using regular expressions with the help of a custom RegexMatch function.
When it comes to restricting user input in Excel worksheets, Data Validation is indispensable. Want to allow only numbers or dates in a given cell? Or limit text values to a specific length? Or maybe disallow times outside a given range? No problem, all this can be easily done with preset or custom validation criteria. But what if I want to allow only valid email addresses or strings that match a specific pattern? Alas, that's not possible. Regex you say? Hmm… that might work!
Regrettably, none of the built-in Excel features support regexes, and Data Validation is no exception. To be able to validate cell input using regular expressions, you need to create a custom Regex function first. Another complication is that VBA user-defined functions cannot be served to Data Validation directly - you'll need a mediator in the form of a named formula.
Considering the above, let us briefly outline the steps to be taken to validate data in Excel using regexes:
Sounds like a plan? Let's try to implement it in practice!
This example addresses a very common case - how to allow only the values of a specific pattern.
Supposing you keep some SKU codes in your worksheet and want to be sure that only the codes matching a given pattern get into the list. Provided that each SKU consists of 2 groups of characters separated with a hyphen, the first group including 3 capital letters and the second group - 3 digits, you can identify such values using the below regex.
Pattern: ^[A-Z]{3}-\d{3}$
Please notice that the start (^) and end ($) of the string are anchored, so no characters other than in the pattern could be entered in a cell.
Start with inserting the RegExpMatch function in your workbook. The code is already written by our Excel gurus, so you just need to copy it from the above-linked page and paste in your VBA editor.
Here's the function's syntax for your reference:
Where:
Tip. If you are a user of our Ultimate Suite, then you can do Regex Data Validation in Excel without adding any VBA code to your workbooks. Just leverage a custom AblebitsRegexMatch function included with our Regex Tools.
In your target worksheet, select cell A1 (regardless of its contents and no matter which cell you are actually going to validate), press Ctrl + F3 to open the Name Manager, and define a name for this formula:
=RegExpMatch(Sheet1!A1, "^[A-Z]{3}-\d{3}$")
Or you can enter the regex in some cell (A2 in this example) and supply $A$2 to the second argument:
=RegExpMatch(Sheet1!A1, Sheet1!$A$2)
For the formula to work correctly, be sure to use a relative reference for the text argument (A1) and absolute reference for pattern ($A$2).
Given that our formula is purposed for validating SKU numbers, we name it accordingly: Validate_SKU.
Important note! When defining the formula, please double check that the first argument refers to the currently selected cell, otherwise the formula won't work. For example, if cell A1 is selected on the sheet, put A1 in the first argument (as per our recommendations); if B2 is selected, then use B2 for the first argument, and so one. It does not really matter which particular reference you use as long as it matches the currently selected cell.
For step-by-step instructions, please see How to make a named formula in Excel.
Select the first cell to be checked (A5 in our case) and make a custom data validation rule based on the named formula. For this, do the following:
=Validate_SKU
Optionally, you can type a custom error message to be displayed when invalid data is entered in a cell.
If you feel like you need the detailed steps, here you go: How to set up custom Data Validation in Excel.
To copy the validation settings to more cells, this is what you need to do:
More information can be found in How to copy Data Validation.
Now, every time someone attempts to enter an invalid SKU in any of the validated cells, the following warning message will appear:
To perform email validation, you start with writing a regular expression that matches an email address.
Pattern: ^[\w\.\-]+@[A-Za-z0-9]+[A-Za-z0-9\.\-]*[A-Za-z0-9]+\.[A-Za-z]{2,24}$
For the detailed explanation of the syntax, please see Regex to match valid email addresses.
And now, specify the validation criteria by performing the already familiar steps:
=RegExpMatch(Sheet1!A1, Sheet1!$B$2)
=Validate_Email
Additionally, you can configure a custom error message prompting the user to enter a valid email address.
If an email address you enter in a validated cell does not match a regex pattern, the following alert will pop up:
When using regex for password validation, the first thing to decide is exactly what your regular expression should check. Here are some examples that might set you on the right track.
A password must be at least 6 characters long and can only contain letters (uppercase or lowercase) and digits:
Pattern: ^[A-Za-z0-9]{6,}$
A password must be minimum 6 characters long and include at least one letter and one digit:
Pattern: ^(?=.*[A-Za-z])(?=.*\d)[A-Za-z\d]{6,}$
A password must be min 6 characters long and include at least one uppercase letter, one lowercase letter and one digit:
Pattern: ^(?=.*[A-Z])(?=.*[a-z])(?=.*\d)[A-Za-z\d]{6,}$
A password must be min 6 characters long and include at least one letter, one digit and one special character:
Pattern: ^(?=.*[A-Za-z])(?=.*\d)(?=.*[@$!%*#?&_-])[A-Za-z\d@$!%*#?&_-]{6,}$
With the pattern established, you can move on to setting up Data Validation:
=RegExpMatch(Sheet1!A1, Sheet1!$C$2)
=Validate_Password
Now, you can safely add new passwords to the list. If an input string does not match the regex, the following alert will remind you what kinds of values are accepted:
If Regex Data Validation does not work in your Excel, most likely it's because of one of the following reasons.
Before applying Data Validation, be sure to insert the code of the RegExpMatch function in your workbook.
To make sure your regex works as expected, you can enter a RegExpMatch formula in some cell and examine the results. For more information, please see Excel Regular expression matching with examples.
To analyze and debug your regular expressions, you can use free online regex testing services such as RegEx101 or RegExr.
A very common reason of data validation failure is a Regex named formula referring to a wrong cell. In all the examples, we recommended to define a formula referring to A1:
=RegExpMatch(A1, regex)
This only works if cell A1 is active when defining a name and a relative reference (without the $ sign) is used.
The idea is that a relative reference specified in the formula (A1) will automatically change based on the relative position of the validated cell. In other words, cell A1 is chosen just for convenience and consistency. In fact, you can select cell B1 and refer to B1, select cell C1 and refer to C1, and so on. The key thing is that the referenced cell should be the active cell.
To check if your named formula is correct, select any cell in your worksheet, open the Name Manager, and see which cell the formula points to. If it refers to the currently selected cell, the formula is right. Otherwise, you should change the reference in the first argument.
In the screenshot below, cell A7 is selected, meaning a named formula should have A7 in the first argument. The second argument ($A$2) refers to the regex - this reference is supposed to remain constant, so it is locked with the $ sign.
When setting up a custom Data Validation rule, it is important to unselect the Ignore blank check box. Otherwise, the rule won't work because of the following reason:
If a match is not found, the RegExpMatch function returns FALSE. With the Ignore blank option selected, FALSE equates to blank and is ignored.
An alternative solution is explicitly stating that the formula should return TRUE:
=RegExpMatch(…)=TRUE
That's how to do Data Validation in Excel using regular expressions. I thank you for reading and look forward to seeing you on our blog next week!
Regex Data Validation examples (.xlsm file)
Table of contents