How to Perform Pattern Matching in Excel (2 Suitable Methods)

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. In Excel, we can determine whether a text follows a pattern. In this article, I will show 2 suitable ways to perform pattern matching in Excel.

Perform Pattern Matching in Excel: 2 Suitable Methods

This is the dataset for today’s article. We have some texts. The pattern we are considering is:

• The total character length will be 9.
• The first 3 characters will be uppercase
• The following 3 characters will be numeric
• The last 3 characters will be lowercase

I will check if the texts follow the pattern or not.

1. Use Combination of Functions

First, I will use a combination of functions to match the pattern. For this purpose, we will be using a combination of the IF, AND, LEN, COUNT, FIND, MID, LEFT, ROW, INDIRECT, and UPPER functions. But before directly applying the formula, we have to create 2 named ranges first.

Steps:

• Go to the Formulas tab.
• After that, select the Name Manager.

• 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
• Then, write the following list in the Refers to

• Press OK.

• Add another named range called numbers with the list of the digits.
• Then, click Close.

• Now, go to C5 and write down the following formula

• Now, press ENTER to get the output.

• After that, use Fill Handle to autofill up to C12.

Read More: How to Use REGEX without VBA in Excel

2. Apply VBA Macro for Pattern Matching

The next method is the use of a VBA Macro for pattern matching. I will create a new function and use this to match the pattern.

The pattern will be

• The first 4 characters will be non-digit.
• The last 4 characters will be digits.

Steps:

• Press ALT+F11 to open the VBA window.
• Then go to Insert >> select Module.

• After that, write down the following code.

Public Function PatternMatch(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
PatternMatch = 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
PatternMatch = storeV
Exit Function
handleER:
PatternMatch = CVErr(xlErrValue)
End Function

• Then, minimize the VBA
• After that, go to C5 and write down the following formula

• Then, press ENTER to get the output.

• After that, use Fill Handle to autofill up to C12.

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

Things to Remember

• You can test other patterns too. The characters have different expressions. For example,
  • \s represents whitespace character
  • \S represents the non-whitespace characters, and so on.
• There are some quantifiers and character classes too.

Download Practice Workbook

Download this workbook and practice while going through the article.

Conclusion

In this article, I have explained 2 suitable methods to perform pattern matching in Excel. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below.

Related Articles

• How to Filter Using RegEx in Excel
• How to Find & Replace Text Using Regex in Excel
• How to Find and Replace RegEx Patterns in Excel
• How to Count Regex with COUNTIF in Excel
• Data Validation with RegEx in Excel
• How to Find RegEx Patterns in Excel

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