Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

Download this workbook and practice while going through the article.


2 Suitable Methods to Perform Pattern Matching in Excel

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

Dataset excel pattern matching

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.

Functions Dataset excel pattern matching

  • Afterward, the New Name wizard will open up.
  • Type Letters in the Name box
  • Then, write the following list in the Refers to
="abcdefghijklmnopqrstuvwxyz"
  • Press OK.

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

Functions Dataset excel pattern matching

  • Now, go to C5 and write down the following formula
=IF(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))),"Matched","Not Matched")

Formula Breakdown

  • COUNT(FIND(MID(LEFT(B5,3), ROW(INDIRECT(“1:”&LEN(LEFT(B5,3)))),1), UPPER(Letters)))=LEN(LEFT(B5,3)) → this part will test if the first 3 characters are uppercase 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 whether the middle 3 characters are numeric digits
  • ISNUMBER(FIND(RIGHT(B5), Letters)) → this segment will check out if the last 3 characters are lowercase letters.
  • LEN(B5)=9 → checks if the total character length is 9
  • The AND function will return TRUE if all of these 4 conditions are fulfilled.
  • Finally, the output will be ‘Matched’ if the logical test is TRUE.
  • Now, press ENTER to get the output.

Functions Dataset excel pattern matching

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

Read More: How to Use REGEX without VBA in Excel (with Easy Steps)


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.

VBA Functions excel pattern matching

  • 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

Code Breakdown

  • Firstly, in the function PatternMatch, we declared val_rng as Range, char_form as
  • The output of this function will be a Variant.
  • Next, 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
  • Then, minimize the VBA
  • After that, go to C5 and write down the following formula
=IF(PatternMatch(B5,"\D{4}\d{4}"),"Matched","Not Matched")

VBA Functions excel pattern matching

Formula Explanation

  • Here, \D{4}\d{4} is the pattern. D{4} represents any non-digit characters, and their position will be the first 4 d{4} represents any digit for the last 4 positions.
  • If the pattern exists, you will get Matched, otherwise Not Matched.
  • Then, press ENTER to get the output.

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

VBA Functions excel pattern matching

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


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.

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. Please visit ExcelDemy for more useful articles like this.


Related Articles

Akib

Akib

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo