How to Use REGEX to Match Patterns in Excel (6 Examples)

If you are trying to find a match with any REGEX pattern to your dataset in Excel, then this article may help you. A REGEX pattern is basically a regular expression that defines any predefined pattern and you can check if each value of your dataset follows this pattern or not. So, let’s get into the main article to know more about this task.


Download Practice Workbook


6 Ways to Use REGEX to Match Patterns in Excel

In the following examples, we will try to show you 6 different ways using which you can easily check any pattern of a regular expression with other values.

For creating this article, we have used Microsoft Excel 365 version. However, you can use any other version at your convenience.


Example-1: Using Combined Formula to Match a REGEX Pattern in Excel

In this section, we will check out the pattern of the following contents of the Pattern column. For this case, our REGEX will be like that the total character length will be 9, the first 3 will be uppercase letters, the next 3 will be numeric values, and the last 3 will be lowercase letters. For this purpose, we will be using a combination of the AND, LEN, COUNT, FIND, MID, LEFT, ROW, INDIRECT, and UPPER functions.

excel regex match by using a formula


Step-01: Creating Dynamic Named Ranges

Firstly, we need to create two named ranges.

  • Go to the Formulas tab >> Defined Names group >> Name Manager.

After that, the Name Manager dialog box will open up.

  • Click on New.

name manager

Afterward, the New Name wizard will open up.

  • Type Letters in the Name box, and the following list in the Refers to
="abcdefghijklmnopqrstuvwxyz"
  • Press OK.

Again, you will get into the Name Manager dialog box.

  • Click on New.

Afterward, the New Name wizard will open up.

  • Type Numbers in the Name box, and the following list in the Refers to
="1234567890"
  • Press OK.

excel regex match by creating named range

After returning to the Name Manager wizard, press Close.


Step-02: Using Formula

Here, we will use our previously created named ranges in a formula.

  • Type the following formula in cell C5.
=AND(LEN(B5)=9, COUNT(FIND(MID(LEFT(B5,3), ROW(INDIRECT("1:"&LEN(LEFT(B5,3)))),1), UPPER(Letters)))=LEN(LEFT(B5,3)), COUNT(FIND(MID(MID(B5,4,3), ROW(INDIRECT("1:"&LEN(MID(B5,4,3)))),1), Numbers))=LEN(MID(B5,4,3)), ISNUMBER(FIND(RIGHT(B5), Letters)))

Formula Breakdown

  • COUNT(FIND(MID(LEFT(B5,3), ROW(INDIRECT(“1:”&LEN(LEFT(B5,3)))),1), UPPER(Letters)))=LEN(LEFT(B5,3)) → this portion will check if the first 3 characters are uppercase letters or not.
  • COUNT(FIND(MID(MID(B5,4,3), ROW(INDIRECT(“1:”&LEN(MID(B5,4,3)))),1), Numbers))=LEN(MID(B5,4,3)) → it will check if the middle 3 characters are numeric digits
  • ISNUMBER(FIND(RIGHT(B5), Letters)) → this portion will check out if the last 3 characters are lowercase letters.
  • LEN(B5)=9 → checks if the total character length is 9
  • Lastly, the AND function will return TRUE if all of these 4 conditions are fulfilled.

excel regex match by using a formula

Finally, you will get TRUE when the pattern matches with REGEX, otherwise FALSE.

Read More: Learn Excel VBA Programming & Macros (Free Tutorial – Step by Step)


Example-2: Getting Results Through Message Boxes for Different Patterns

Here, we will define a text pattern with a REGEX pattern in a VBA code to match the pattern and then extract our desired portion through a message box.


Step-01: Launching VBA Editor

  • Go to the Developer tab >> Visual Basic.

launching VBA Editor for excel regex match

After that, the Visual Basic Editor window will appear.

  • Go to the Insert tab >> Module.

Then, we will have a new module like Module 1.

  • Go to the Tools tab >> References.

launching VBA Editor for excel regex match

Afterward, the References-VBAProject window will appear.

  • Click on Microsoft VBScript Regular Expressions 5.5.
  • Press OK.

It will enable the regEx object.


Step-02: Writing Code in Module

  • Type the following code in your newly created module.
Sub match_pat_1()
Dim char_form, char_renew, char_data As String
Dim regEx As New RegExp
char_form = "^[A-Z]{1,4}"
char_renew = ""
If char_form <> "" Then
char_data = "ABCD6758"
With regEx
.IgnoreCase = False
.Pattern = char_form
End With
If regEx.Test(char_data) Then
MsgBox (regEx.Replace(char_data, char_renew))
Else
MsgBox ("Can't find match")
End If
End If
End Sub

Code Breakdown

  • Here, we have declared char_form, char_renew, char_data as String, and regEx as New RegExp.
  • Then, we assigned our regular expression pattern “^[A-Z]{1,4}” to the char_form variable, and it represents that the first 4 letters should be capital letters. Later, char_renew is assigned to blank.
  • Using the IF statement we created the next codes for any non-blank characters.
  • The input data “ABCD6758” is assigned to the char_data
  • Using the WITH statement we characterized the components of the regEx
  • Test(char_data) will search for our given pattern to this input data, and if it matches then we will get TRUE. It executes the following line with the REPLACE function which replaces the first 4 characters with a blank.
  • If we get FALSE, the MsgBox will give us “Can’t find match”.

using code for excel regex match

  • Press F5.

Afterward, the message box will return 6758.

  • You can also use the following code if you want to extract the last 4 characters as letters.
Sub match_pat_1()
Dim char_form, char_renew, char_data As String
Dim regEx As New RegExp
char_form = "^[0-9]{1,4}"
char_renew = ""
If char_form <> "" Then
char_data = "6758ABCE"
With regEx
.IgnoreCase = False
.Pattern = char_form
End With
If regEx.Test(char_data) Then
MsgBox (regEx.Replace(char_data, char_renew))
Else
MsgBox ("Can't find match")
End If
End If
End Sub

Here, “^[0-9]{1,4}” is our desired pattern representing that the first 4 characters should be numeric data. And “6758ABCE” is our input data.

using code for excel regex match

  • After pressing F5, we will get the result as ABCE.

  • For extracting the last 6 characters including texts and numbers, we used the following code.
Sub match_pat_1()
Dim char_form, char_renew, char_data As String
Dim regEx As New RegExp
char_form = "^[0-9]{1,2}"
char_renew = ""
If char_form <> "" Then
char_data = "6758ABCE"
With regEx
.IgnoreCase = False
.Pattern = char_form
End With
If regEx.Test(char_data) Then
MsgBox (regEx.Replace(char_data, char_renew))
Else
MsgBox ("Can't find match")
End If
End If
End Sub

Here,  “^[0-9]{1,2}” is our desired pattern representing that the first 2 characters should be numeric data. And “6758ABCE” is our input data.

  • After pressing F5, we will get the result 58ABCE.

Read More: List of 10 Mostly Used Excel VBA Objects (Attributes & Examples)


Example-3: Create VBA Function to Match REGEX and Extract a Portion in Excel

Here, we will create a function with the help of a VBA code to match the REGEX pattern in Excel and then extract the characters after the first 4 letters.

Create VBA Function to Match REGEX and Extract a Portion in Excel

Steps:

  • Follow Step-01 of Example-2.
  • Now write down the following code in your created module.
Function match_pat(val_rng As Range) As String
Dim char_form, char_renew, char_data As String
Dim regEx As New RegExp
char_form = "^[A-Za-z]{1,4}"
char_renew = ""
If char_form <> "" Then
char_data = val_rng.Value
With regEx
.IgnoreCase = False
.Pattern = char_form
End With
If regEx.Test(char_data) Then
match_pat = regEx.Replace(char_data, char_renew)
Else
match_pat = " "
End If
End If
End Function

Code Breakdown

  • Firstly, in the function match_pat, we declared val_rng as Range and the output of this function will be a string.
  • Here, we have declared char_form, char_renew, char_data as String, and regEx as New RegExp.
  • Then, we assigned our regular expression pattern “^[A-Za-z]{1,4}” to the char_form variable, and it represents that the first 4 letters should be lowercase or uppercase letters. Later, char_renew is assigned to blank.
  • Using the IF statement we created the next codes for any non-blank characters.
  • The input data range val_rng is assigned to the char_data
  • Using the WITH statement we characterized the components of the regEx
  • Test(char_data) will search for our given pattern to this input data, and if it matches then we will get TRUE. It executes the following line with the REPLACE function which replaces the first 4 characters with a blank.
  • If we get FALSE, then we will get a blank.

Now, return to your main worksheet.

  • Type the following formula in cell C5.
=match_pat(B5)

Here, B5 is our input data, and match_pat will return the characters after the first 4 letters.

  • Drag down Fill Handle.

Create VBA Function to Match REGEX and Extract a Portion in Excel

Finally, you will get the following results in the Extracted Portion column.

Read More: How to Use VBA Input Function in Excel (2 Examples)


Similar Readings


Example-4: Extraction of a Definite Pattern after Matching REGEX for a Loop

Here, with the help of a VBA code, we will match the data of the Pattern column with the REGEX pattern in Excel and then extract the characters after the first 4 letters.

Excel REGEX Match within a loop

Steps:

  • Follow Step-01 of Example-2.
  • Now write down the following code in your created module.
Sub match_pat_2()
Dim char_form, char_renew, char_data As String
Dim regEx As New RegExp
Dim val_rng As Range
Dim Val As Variant
Set val_rng = Range("B5:B12")
char_form = "^[A-Za-z]{1,4}"
char_renew = ""
For Each Val In val_rng
If char_form <> "" Then
char_data = Val.Value
With regEx
.IgnoreCase = False
.Pattern = char_form
End With
If regEx.Test(char_data) Then
Val.Offset(0, 1).Value = regEx.Replace(char_data, char_renew)
Else
Val.Offset(0, 1).Value = " "
End If
End If
Next
End Sub

Code Breakdown

  • Here, we have declared char_form, char_renew, char_data as String, and regEx as New RegExp.
  • The range B5:B12 will be assigned to a variable val_rng.
  • Then, we assigned our regular expression pattern “^[A-Za-z]{1,4}” to the char_form variable, and it represents that the first 4 letters should be lowercase or uppercase letters. Later, char_renew is assigned to blank.
  • Later, we used the FOR NEXT Loop to go through each cell of the val_rng.
  • Using the IF statement we created the next codes for any non-blank characters.
  • The input data range val_rng is assigned to the char_data
  • Using the WITH statement we characterized the components of the regEx
  • Test(char_data) will search for our given pattern to this input data, and if it matches then we will get TRUE. It executes the following line with the REPLACE function which replaces the first 4 characters with a blank.
  • If we get FALSE, then we will get a blank.
  • Press F5.

In this way, you will get the output results in the Extracted Portion column.

Excel REGEX Match within a loop

Read More: Introduction to VBA Features and Applications


Example-5: Splitting Values Based on Pattern in Excel

Here, we will split the values of the Pattern column into 3 parts, the first portion will have the first 4 digits, the second portion will contain the next 2 letters, and the last part will contain the last 4 digits.

splitting values after Excel REGEX Match

Steps:

  • Follow Step-01 of Example-2.
  • Now write down the following code in your created module.
Sub match_pat_3()
Dim char_form, char_data As String
Dim regEx As New RegExp
Dim val_rng As Range
Dim Val As Variant
Set val_rng = Range("B5:B12")
For Each Val In val_rng
char_form = "([0-9]{4})([a-zA-Z]{2})([0-9]{4})"
If char_form <> "" Then
char_data = Val.Value
With regEx
.IgnoreCase = False
.Pattern = char_form
End With
If regEx.Test(char_data) Then
Val.Offset(0, 1).Value = regEx.Replace(char_data, "$1")
Val.Offset(0, 2).Value = regEx.Replace(char_data, "$2")
Val.Offset(0, 3).Value = regEx.Replace(char_data, "$3")
Else
Val.Offset(0, 1).Value = " "
End If
End If
Next
End Sub

Code Breakdown

  • Here, we have declared char_form, char_renew, char_data as String, and regEx as New RegExp.
  • The range B5:B12 will be assigned to a variable val_rng.
  • Later, we used the FOR NEXT loop to go through each cell of the val_rng.
  • Then, we have assigned our regular expression pattern “([0-9]{4})([a-zA-Z]{2})([0-9]{4})” to the char_form variable, and it represents that the first 4 characters should be numeric digits, the next 2 characters should be lowercase or uppercase letters, and the last 4 digits should be numeric values.
  • Using the IF statement we created the next codes for any non-blank characters.
  • The input data range val_rng is assigned to the char_data
  • Using the WITH statement we characterized the components of the regEx
  • Test(char_data) will search for our given pattern to this input data, and if it matches then we will get TRUE. Then, we split the characters into the next 3 columns.
  • If we get FALSE, then we will get a blank.

  • Press F5.

Eventually, we split our data from the Pattern column into the next 3 columns when the data of this column matches our pattern.

splitting values after Excel REGEX Match

Read More: 22 Macro Examples in Excel VBA


Example-6: Checking REGEX Patterns Through User-Defined Function in Excel

In this section, we will create a ubiquitous function to match the REGEX pattern in Excel, and while functioning it will take the pattern to be matched by the user.

Steps:

  • Follow Step-01 of Example-2.
  • Now write down the following code in your created module.
Function matchP(val_rng As Range, char_form As String) As Variant
Dim storeV() As Variant
Dim limit_1, limit_2, R_count, C_count As Long
On Error GoTo handleER
matchP = storeV
Set regEx = CreateObject("VBScript.RegExp")
With regEx
.IgnoreCase = False
.pattern = char_form
End With
R_count = val_rng.Rows.Count
C_count = val_rng.Columns.Count
ReDim storeV(1 To R_count, 1 To C_count)
For limit_1 = 1 To R_count
For limit_2 = 1 To C_count
storeV(limit_1, limit_2) = regEx.Test(val_rng.Cells(limit_1, limit_2).Value)
Next
Next
matchP = storeV
Exit Function
handleER:
matchP = CVErr(xlErrValue)
End Function

Code Breakdown

  • Firstly, in the function matchP, we declared val_rng as Range, char_form as String and the output of this function will be a Variant.
  • Here, we declared storeV() as Variant, and limit_1, limit_2, R_count, C_count as Long.
  • Then, we assigned the array storeV to the function.
  • Later, the object regEx is declared, and using the WITH statement we characterized the components of the regEx
  • R_count, and C_count will count the total number of rows and columns of our input range. Later, we resized our previously declared dynamic array storeV() up to these numbers.
  • After that, using two FOR loops we will test the values of our input range up to the number of total rows and total columns. And then the result will be stored in the storeV() array which will give us the output result.

User Defined Function for Excel REGEX Pattern Match


6.1. Checking Any Combination of Numbers and Texts

Here, we will check out the pattern of the combination of numbers and letters in the values of the Pattern column.

Steps:

  • Write down the following formula in cell C5.
=matchP(B5,"\D{4}\d{4}")

Here, \D{4}\d{4} is the pattern. D{4} represents any non-digit characters, and their position will be the first 4 positions. d{4} represents any digit for the last 4 positions.

  • Drag down Fill Handle.

User Defined Function for Excel REGEX Pattern Match

Then, you will get TRUE for the patterns which have alphabets in the first 4 positions and digits in the last 4 positions.

  • You can also do the above task using the following formula.
=matchP(B5,"\w{8}")

Here, the pattern w{8} represents any alphanumeric character up to 8 positions.

Read More: Excel VBA to Populate Array with Cell Values (4 Suitable Examples)


6.2. Checking Phone Number Patterns

In this section, we will check out the patterns of phone numbers by matching them with a REGEX pattern in Excel. In some phone numbers, the first three digits remain within brackets or not. Generally, the total 10 digits of a phone number are divided into 3 groups. Each group is divided by a space, comma, or dot. So, in making a pattern we have to maintain these criteria.

Steps:

  • Enter the following formula.
=matchP(B5,"(\(\d{3}\)|\d{3})[-\.\s]?\d{3}[-\.\s]?\d{4}")

Pattern Breakdown

  • \(\d{3}\) → represents if the first 3 digits are inside the bracket
  • |\d{3} → It is indicating another option that the first 3 digits are without bracket.
  • [-\.\s]? → Here using the ? symbol we are indicating that , ., or space characters will appear 0 times or a single time.
  • Then, d{3} is the next group of 3 digits, and d{4} is the group of the last 4.

User Defined Function for Excel REGEX Pattern Match

Read More: How to Use VBA User Defined Function (4 Suitable Examples)


6.3. Checking Email Patterns

Here, we will check out patterns of different email addresses by matching them with a REGEX pattern in Excel.

Steps:

  • Enter the following formula.
=matchP(B5,"[\w\.\-][email protected][A-Za-z0-9]+[A-Za-z0-9\.\-]*[A-Za-z0-9]+")

Pattern Breakdown

  • [\w\.\-]+represents the first portion of an email id which may contain a name or some digits or . or a
  • @[A-Za-z0-9]+[A-Za-z0-9\.\-]*[A-Za-z0-9]+ it is representing the portion after @ symbol, which is the domain name. In this portion, we may see different uppercase or lowercase letters with numbers, hyphens, dots, etc. Here, + and * represents multiple occurrences.

User Defined Function for Excel REGEX Pattern Match

Read More: How to Put Comma After 2 Digits in Excel (9 Quick Methods)


Practice Section

To practice by yourself, we have created a Practice section on the right side of each sheet.

Practice


Conclusion

In this article, we have discussed different ways to find a match with any REGEX pattern to your dataset in Excel. Hope these methods will help you a lot. If you have any further queries, then leave a comment below.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo