# 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

### Method 1 – Combining Functions

Use the IF, AND, LEN, COUNT, FIND, MID, LEFT, ROW, INDIRECT, and UPPER functions.

Create 2 named ranges.

Steps:

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

• In the Name Manager dialog box, click New.

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

• Click Close.

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

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

Steps:

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

• 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)
Next
Next
PatternMatch = storeV
Exit Function
handleER:
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")`

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.

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

## 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, 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