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

Akib,

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

Thanks much

Hello

Earlington Etienne,You are most welcome.

Regards

ExcelDemy