How to Perform Pattern Matching in Excel – 2 Methods


This is the sample dataset.

Check if the texts follow this pattern:

  • The total character length is 9.
  • The first 3 characters are uppercase
  • The following 3 characters are numeric
  • The last 3 characters are lowercase

Dataset excel pattern matching


Method 1 – Combining Functions


Create 2 named ranges.


  • Go to the Formulas tab.
  • Select Name Manager.

  • In the Name Manager dialog box, click New.

Functions Dataset excel pattern matching

  • In the New Name wizard, enter Letters in Name.
  • Enter the following list in Refers to
  • Click OK.

  • Add another named range: Numbers. Add the list of digits.
  • Click Close.

Functions Dataset excel pattern matching

  • Go to C5 and enter 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)) → tests if the first 3 characters are uppercase.
  • COUNT(FIND(MID(MID(B5,4,3), ROW(INDIRECT(“1:”&LEN(MID(B5,4,3)))),1), Numbers))=LEN(MID(B5,4,3)) → checks whether the middle 3 characters are numeric digits
  • ISNUMBER(FIND(RIGHT(B5), Letters)) → checks if the last 3 characters are lowercase.
  • LEN(B5)=9 → checks if the total character length is 9
  • The AND function returns TRUE if the 4 conditions are met.
  • The output will be ‘Matched’ if the logical test is TRUE.
  • Press ENTER to see the output.

Functions Dataset excel pattern matching

  • Drag down the Fill Handle to see the result in the rest of the cells.

Read More: How to Use REGEX without VBA in Excel

Method 2 – Applying a VBA Macro for Pattern Matching

The pattern is:

  • The first 4 characters are non-digit.
  • The last 4 characters are digits.


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

VBA Functions excel pattern matching

  • Enter 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)
PatternMatch = storeV
Exit Function
PatternMatch = CVErr(xlErrValue)
End Function

Code Breakdown

  • In PatternMatch, val_rng is declared as Range, char_form as
  • The output of this function will be a Variant.
  • storeV() is declared as Variant, and limit_1, limit_2, R_count, C_count as Long.
  • The array storeV is assigned to the function.
  • regEx is declared, and the WITH statement characterizes the components of the regEx
  • R_count, and C_count will count the total number of rows and columns in the input range. The declared dynamic array storeV() is resized to these numbers.
  • Two FOR loops test the values of the input range up to the total rows and columns. The result is stored in the storeV() array, which returns the output.
  • Minimize the VBA
  • Go to C5 and enter the following formula.
=IF(PatternMatch(B5,"\D{4}\d{4}"),"Matched","Not Matched")

VBA Functions excel pattern matching

Formula Explanation

  • \D{4}\d{4} is the pattern. D{4} represents non-digit characters, and their position will be the first 4;  d{4} represents digit in the last 4 positions.
  • If the pattern exists, it returns Matched, otherwise Not Matched.
  • Press ENTER to see the output.

  • Drag down the Fill Handle to see the result in the rest of the cells.

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
    • \S represents the non-whitespace characters

Download Practice Workbook

Download this workbook and practice.


Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
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

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


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

    Thanks much

Leave a reply

Advanced Excel Exercises with Solutions PDF