Data Validation with RegEx in Excel (With Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

In Microsoft Excel, there is no inbuilt function named RegEx. Rather we have to create it manually in the workbook. This function is required to prohibit the input of any invalid character in your worksheet. It only returns the regular expressions encoded. In this article, let us learn data validation with RegEx in Excel with some easy steps. For a better understanding, we need to know about the RegEx function in detail as well.


What Is RegEx Function?

When your dataset follows certain patterns with special sequences of characters and symbols, it creates a Regular Expression (RegEx). Holding these characters, when we create a function with arguments, it is called the RegEx function. It helps to match any input and validate it according to the encrypted code.

The RegEx function syntax consists of many special characters, quantifiers, operators etc. It is mostly used in programming languages like VBA, and JavaScript.


Components of RegEx Patterns

Any RegEx pattern consists of more than one of the following components:

  • Characters: Frequently used patterns that match specified characters (\w, \d, \t etc).
  • Qualifiers: It allows you to change the basic pattern specifying the number of times that you want to match (*, ?, {n} etc).
  • Anchors: It determines the position of the input string for matching (^, $ etc).
  • Look-arounds: It is helpful to match a pattern that is or isn’t followed by any external condition {(?=), (?<=) etc}.
  • Grouping: It develops a code to capture any substring along with the main string from the source {(?:pattern), \2 etc}.

Data Validation with RegEx in Excel: Step-by-Step Procedures

So far, we have gone through some detailed understanding of RegEx. It is necessary before operating the process of validation. Now, let’s jump into the steps for data validation with RegEx in Excel.


STEP 1: Prepare Dataset with Pattern

For validating with RegEx, we need a create a sample dataset.

  • First, insert the below Product ID Pattern in Cell C4 as per your requirement.
^[A-Z]{2}-\d{4}$

Prepare Dataset with Pattern

From the pattern, we can get the information that, it consists of two groups of characters. The first one is 2 lettered alphabets and the second one is 4 numerical values. Both of them are separated with a Hyphen (). To restrict it from additional pattern input, it is anchored in the beginning and in the end with these (^, $) symbols.

  • Then, type some Product IDs in the Cell range B7:B9 following the pattern that we developed initially.

Read More: How to Perform Pattern Matching in Excel


STEP 2: Create RegEx Function with VBA

This is the most important part of the article. Here, we will develop a VBA code that will generate the RegEx function. To do the task, follow the steps below.

  • First, go to the Developer tab and select Visual Basic from the Code section.

Create RegEx Function with VBA

  • Then, select Module from the Insert tab in the Microsoft Visual Basics for Applications window.

  • Next, insert this code in the blank page.
Public Function RegExValidation(insert_range As Range, ptrn As String, Optional match_event As Boolean = True) As Variant
  Dim axRes() As Variant
  Dim xInputCurRow, xInputCurCol, ctInputRows, ctInputCols As Long
  On Error GoTo ErrorHandler
  RegExValidation = axRes
  Set xRegEx = CreateObject("VBScript.RegExp")
  xRegEx.pattern = ptrn
  xRegEx.Global = True
  xRegEx.MultiLine = True
  If True = match_event Then
    xRegEx.ignorecase = False
  Else
    xRegEx.ignorecase = True
  End If
  ctInputRows = insert_range.Rows.Count
  ctInputCols = insert_range.Columns.Count
  ReDim axRes(1 To ctInputRows, 1 To ctInputCols)
  For xInputCurRow = 1 To ctInputRows
    For xInputCurCol = 1 To ctInputCols
      axRes(xInputCurRow, xInputCurCol) = RegEx.Test(insert_range.Cells(xInputCurRow, xInputCurCol).Value)
    Next
  Next
  RegExValidation = axRes
  Exit Function
ErrorHandler:
    RegExValidation = CVErr(xlErrorValue)
End Function

Create RegEx Function with VBA

  • After this, save the code by pressing Ctrl + S and then, close the window.
  • As a result, you will get the RegExValidation function with this syntax:
=RegExValidation(text, insert_range, [match_event])

Here the arguments are as follows:

Text: One or multiple strings to search.

Insert_range: The regular expression for matching with the string.

Match_event: It is an optional argument that can be either True or False based on case-sensitive matching.

Read More: How to Use REGEX without VBA in Excel


STEP 3: Name New Formula

Now, as we have our function, we will create a formula and name it to establish in the workbook. To do this, follow the steps below.

  • In the beginning, select any cell in your dataset. For example, we selected Cell B4.
  • Then, go to the Formulas tab and click on Name Manager in the Defined Names section.
  • You can also press Ctrl + F3 to open it.

Name New Formula

  • Next, select New in the Name Manager dialogue box.

  • Afterward, enter any name according to your preference for the formula in the Name box.
  • Along with it, insert this formula in the Refers to box and press OK.
=RegExValidation(Data Validation with RegEx!C4, Data Validation with RegEx!$C$4)

Name New Formula

Note: The first cell has to be the cell that we selected earlier and it will be a Relative Reference. The second cell can be any cell in the worksheet but it must be an Absolute Reference.
  • Now, you will see the formula is named and listed.
  • Following this, press Close to turn off the window.


STEP 4: Create Custom Data Validation Rule

As we have encrypted the formula for this workbook, now let us apply a data validation rule based on the formula.

  • First, select Cell B7 as it is the first cell of our dataset.

Create Custom Data Validation Rule

  • Then, select Data Validation under the Data Tools group in the Data tab.

  • In the Data Validation window, select Custom in the Allow box.
  • Also, mark unchecked the Ignore blank box.

  • Along with it, insert the name of the formula in the Formula box that we created earlier in Step 3.

Create Custom Data Validation Rule

  • In addition to this, you can create an Error message with a Title in the Error Alert tab.


STEP 5: Copy Data Validation

In this stage, we will copy the data validation rule into other cells of the workbook.

  • In the beginning, right-click on Cell B7 and select Copy.

Copy Data Validation

  • Then, select any number of cells where you want to apply the Data Validation rule.
  • Afterward, right-click on them and select Paste Special.

  • Next, select Validation in the Paste Special window and press OK.


STEP 6: Proof Checking

Finally, we have successfully validated RegEx in the workbook. To check if it working properly or not, you can try any of the following options.

Option 1: Proof Checking with Error Message

An error message will prove the success in this case.

  • To do this, type a new Product ID in Cell B10.

Proof Checking

  • Afterward, press Enter and you will get this error message.

  • It shows that the text is not matching with the RegEx pattern that we validated.

Option 2: Proof Checking with Formula

Another way is to check with the RegEx formula that we created earlier. This process is applicable when you do not copy the Data Validation rule to other cells.

  • To do this, type this formula in Cell C7.
=RegExValidation(B7,$C$4,TRUE)

  • Then, press Enter > Autofill.
  • As a result, it will determine whether the text is matching or not with the RegEx pattern as TRUE or FALSE like this.

Excel RegEx Validation

Read More: How to Use REGEX to Match Patterns in Excel


What to Do When RegEx Validation Is Not Working in Excel?

One of the following reasons might be responsible for RegEx validation not working.

1. Wrong Cell Reference

During naming the formula in Step 3, if you provide any other cell than the active cell as a Relative Reference, the validation will not work.

You can check and correct it in the Name Manager window.

2. Ignore Blank Option Enabled

During applying the Data Validation rule, sometimes people may forget to mark uncheck the Ignore Blank option. As a result, it returns a blank cell and is ignored when a match is not found. Therefore, you can type this formula instead in the Name Manager window if you enable the option.

=RegExValidation(Data Validation with RegEx!C4, Data Validation with RegEx!$C$4)=True

Download Practice Workbook

Get this sample file to practice by yourself.


Conclusion

Finally, we are concluding our long article. We tried to illustrate the process of Data Validation with Regular Expression or RegEx in Excel through some simple steps. I hope you will find it helpful.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Mehrun Guria
Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo