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. 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.

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.

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.

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.

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.

Drag down the Fill Handle tool.

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

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.

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.

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”.

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.

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.

3. Creating a 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.

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 the Fill Handle.

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

Read More: How to Filter Using RegEx in Excel

4. Extracting 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.

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.

5. Splitting Values Based on Patterns 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.

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.

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.

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.

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.

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.

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\.\-]+@[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.

Practice Section

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

Download Practice Workbook

REGEX Pattern.xlsm

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.

<< Go Back to RegEx in Excel | String Manipulation | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

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

1. Using Combined Formula to Match a REGEX Pattern in Excel

Step-01: Creating Dynamic Named Ranges

Step-02: Using Formula

2. Getting Results Through Message Boxes for Different Patterns

Step-01: Launching VBA Editor

Step-02: Writing Code in Module

3. Creating a VBA Function to Match REGEX and Extract a Portion in Excel

4. Extracting a Definite Pattern after Matching REGEX for a Loop

5. Splitting Values Based on Patterns in Excel

6. Checking REGEX Patterns Through User-Defined Function in Excel

6.1. Checking Any Combination of Numbers and Texts

6.2. Checking Phone Number Patterns

6.3. Checking Email Patterns

Practice Section

Conclusion

Related Articles

Leave a reply Cancel reply