This example addresses a very common case - how to allow only the values of a specific pattern.

Sounds like a plan? Let's try to implement it in practice! Excel data validation using custom regular expressions

Copy the validation settings to as many cells as you want.

Configure a Data Validation rule based on the named formula.

Create a custom Regex function that checks whether an input value matches a regular expression.

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.Ĭonsidering the above, let us briefly outline the steps to be taken to validate data in Excel using regexes: To be able to validate cell input using regular expressions, you need to create a custom Regex function first. Regrettably, none of the built-in Excel features support regexes, and Data Validation is no exception. How to do Excel Data Validation with Regex

How to do Excel Data Validation with Regex.

But what if I want to allow only valid email addresses or strings that match a specific pattern? Alas, that's not possible. 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.

When it comes to restricting user input in Excel worksheets, Data Validation is indispensable. The tutorial shows how to do data validation in Excel using regular expressions with the help of a custom RegexMatch function.

Mac excel 2013 data validation