Identifying Values that Don't Follow a Specific Pattern

Vishwajeet has a series of part numbers in a column. These part numbers must follow a specifc pattern (2 numbers, 5 letters, 4 numbers, 1 letter, 1 number, 1 letter, and 1 number). He wonders if there is a way to easily identify which of the cells in the column vary from this pattern.

There are a number of ways you can approach this task, depending on the true nature of your data. For instance, you could use a formula like this in a helper column:

=AND(LEN(A1)=15,ISNUMBER(--LEFT(A1,2)),ISTEXT(MID(A1,3,5)),
ISNUMBER(--MID(A1,8,4)),ISTEXT(MID(A1,12,1)),ISNUMBER(--
MID(A1,13,1)),ISTEXT(MID(A1,14,1)),ISNUMBER(--RIGHT(A1,1)))

The formula (which is quite long) returns True or False, depending on whether the pattern is correct or not. There is a problem with the formula, however. It won't catch symbols used in place of letters (such as a dollar sign or an asterisk) and it won't catch some symbols used in place of numbers (such as a period or a percent sign). The reason is that the ISTEXT function considers symbols to be text and the ISNUMBER function parses something like "1.23" as a number.

If you want to catch this improper use of symbols, the following formula could be used:

=AND(LEN(A1)=15,ISNUMBER(SUM(SEARCH(MID(A1,{1,2,8,9,10,11,
13,15},1),"0123456789"),SEARCH(MID(A1,{3,4,5,6,7,12,14},1),
"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))))

This one works because it uses the SEARCH function to effectively check every character in the part number.

If you need to check the part number patterns quite a bit, you may want to consider using a macro to do the checking. The following is a short user-defined function that uses the Like operator to see if the pattern is followed.

Function CheckPattern(rCell As Range) As Boolean
    Dim sPattern As String

    sPattern = "##[A-Z][A-Z][A-Z][A-Z][A-Z]####[A-Z]#[A-Z]#"

    CheckPattern = rCell.Value Like sPattern
End Function

Note the use of the sPattern variable. This is the pattern to be followed when the Like operator does its comparison. Each occurrence of the # symbol means that any digit can be in this position. Each occurrence of [A-Z] means that the position can be one letter in the range of A to Z.

You can find more about the meaning of the characters you can include in the pattern by visiting this page at one of Microsoft's sites:

https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/operators/like-operator

This site is actually for Visual Basic, not VBA, but this particular page of information will work just fine in VBA.

To use the CheckPattern user-defined function, you can place the following in any cell in your worksheet:

=CheckPattern(A1)

This assumes that the part number is in cell A1, as do all the other formulas presented in this tip.

You could also utilize the UDF in a conditional formatting rule to easily highlight cells that don't follow the pattern. I won't go into how to create such a rule here, as I've done it elsewhere in ExcelTips. All you need to do, however, is set up a rule that uses a formula and then use the following as the formula:

=AND(CheckPattern(A1)=FALSE,A1<>"")

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3391) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.