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

Get FREE Advanced Excel Exercises with Solutions!

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

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


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


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Akib Bin Rashid
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

2 Comments
  1. Reply
    Earlington Etienne Jan 19, 2024 at 1:14 AM

    Akib,

    This is awesome. Especially the array formula using Row() with “1:3”.

    Thanks much

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo