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.
- Add another named range: Numbers. Add the list of digits.
- 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.
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
- 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
Get FREE Advanced Excel Exercises with Solutions!
Akib,
This is awesome. Especially the array formula using Row() with “1:3”.
Thanks much
Hello Earlington Etienne,
You are most welcome.
Regards
ExcelDemy