Verifying a specific format of the cell value

[protrader]

Hi, I need to check whether the values in column C have specific format and then execute some commands. The specific format is two or three letters, one dash and 3 numbers, for example:

AB-123
ABC-345
ZZ-001
RE-909

Is there any way to do this in Excel VBA?

 

[Jon von der Heyden]

Welcome to the board.

You could use the Like operator and check it against patterns. For example here is a function that allows you to pass a number of different pattern strings. If it matches any then it returns True.

Public Function TestPattern(ByVal strValue As String, ParamArray varPattern() As Variant) As Boolean
    Dim varPatterns As Variant
    Dim lngItem As Long
    
    varPatterns = varPattern
    For lngItem = LBound(varPatterns) To UBound(varPatterns)
        TestPattern = strValue Like varPatterns(lngItem)
        If TestPattern = True Then Exit Function
    Next lngItem
End Function

You can call this in your code, e.g:

Public Sub demo()
    Dim blnMatch As Boolean
    blnMatch = TestPattern(Range("A1").Value, "[A-Z][A-Z]-###", "[A-Z][A-Z][A-Z]-###")
    MsgBox blnMatch
End Sub

Or even use it as a worksheet function, e.g:
=TestPattern(A1,"[A-Z][A-Z]-###","[A-Z][A-Z][A-Z]-###")

 

[hiker95]

protrader,

Welcome to the MrExcel forum.

Sample raw data:

Excel 2007
	C	D
1	AB-123
2	ABC-345
3	ZZ-001
4	RE-909
5	A-12
6	ABCD-123
7	ABC-34
8	Z-001
9	A-1
10	AB-123
11	ABC-345
12
Sheet1

After the macro:

Excel 2007
	C	D
1	AB-123	Format Matches
2	ABC-345	Format Matches
3	ZZ-001	Format Matches
4	RE-909	Format Matches
5	A-12	No Match
6	ABCD-123	No Match
7	ABC-34	No Match
8	Z-001	No Match
9	A-1	No Match
10	AB-123	Format Matches
11	ABC-345	Format Matches
12
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub CheckFormat()
' hiker95, 10/21/2013
' http://www.mrexcel.com/forum/excel-questions/734062-verifying-specific-format-cell-value.html
Dim c As Range
For Each c In Range("C1", Range("C" & Rows.Count).End(xlUp))
  If c Like "[A-Z][A-Z]-###" Or c Like "[A-Z][A-Z][A-Z]-###" Then
    c.Offset(, 1) = "Format Matches"
  Else
    c.Offset(, 1) = "No Match"
  End If
Next c
Columns("D").AutoFit
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CheckFormat macro.