How to test for specific characters with regex in VBA

Question

I need to test for a string variable to ensure it matches a specific format:

XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX

...where x can be any alphanumerical character (a - z, 0 - 9).

I've tried the following, but it doesn't seem to work (test values constantly fail)

If val Like "^([A-Za-z0-9_]{8})([-]{1})([A-Za-z0-9_]{4})([-]{1})([A-Za-z0-9_]{4})([-]{1})([A-Za-z0-9_]{4})([-]{1})([A-Za-z0-9_]{12})" Then
    MsgBox "OK"
Else
    MsgBox "FAIL"
End If

.

fnCheckSubscriptionID "fdda752d-32de-474e-959e-4b5bf7574436"

Any pointers? I don't mind if this can be achieved in vba or with a formula.

score 2 · Accepted Answer · answered Jun 14 '14 at 21:43

You are already using the ^ beginning-of-string anchor, which is terrific. You also need the $ end-of-string anchor, otherwise in the last group of digits, the regex engine is able to match the first 12 digits of a longer group of digits (e.g. 15 digits).

I rewrote your regex in a more compact way:

^[A-Z0-9]{8}-(?:[A-Z0-9]{4}-){3}[A-Z0-9]{12}$

Note these few tweaks:

[-]{1} can just be expressed with -
I removed the underscores as you say you only want letters and digits. If you do want underscores, instead of [A-Z0-9]{8} (for instance), you can just write \w{8} as \w matches letters, digits and underscores.
Removed the lowercase letters. If you do want to allow lowercase letters, we'll turn on case-insensitive mode in the code (see line 3 of the sample code below).
No need for (capturing groups), so removed the parentheses
We have three groups of four letters and a dash, so wrote (?:[A-Z0-9]{4}-) with a {3}

Sample code

Dim myRegExp, FoundMatch
Set myRegExp = New RegExp
myRegExp.IgnoreCase = True
myRegExp.Pattern = "^[A-Z0-9]{8}-(?:[A-Z0-9]{4}-){3}[A-Z0-9]{12}$"
FoundMatch = myRegExp.Test(SubjectString)

I love the complex simplicity of regex and this implementation of it. Ciao. — aSystemOverload, Jun 15 '14 at 21:48
If you're interested in regex tricks, this one applies to many situations, and you might enjoy it on a rainy day: [Match (or replace) a pattern except when...](http://stackoverflow.com/questions/23589174/match-or-replace-a-pattern-except-in-situations-s1-s2-s3-etc/23589204#) I had a lot of fun writing it. — zx81, Jun 15 '14 at 21:54

omegastripes · Answer 2 · 2014-06-14T21:41:47.130

1

Sub Test()
    MsgBox fnCheckSubscriptionID("XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX")
End Sub

Function fnCheckSubscriptionID(strCont)
    ' Tools - References - add "Microsoft VBScript Regular Expressions 5.5"
    With New RegExp
        .Pattern = "^\w{8}-\w{4}-\w{4}-\w{4}-\w{12}$"
        .Global = True
        .MultiLine = True
        fnCheckSubscriptionID = .Test(strCont)
    End With
End Function

In case of any problems with early binding you can use late binding With CreateObject("VBScript.RegExp") instead of With New RegExp.

edited Jun 14 '14 at 21:41

answered Jun 14 '14 at 18:31

omegastripes

12,351
4
45
96

1

Your idea is OK, but \w = [A-Za-z0-9_] so both the \d and the .ignorecase are redundant. It is also recommended to explicitly declare your variables. Setting Option Explicit will make your code more efficient and also help in catching misspellings. Good discussion here: http://www.cpearson.com/excel/declaringvariables.aspx – Ron Rosenfeld Jun 14 '14 at 21:02

score 1 · Answer 3 · answered Jun 14 '14 at 20:54

You can do this either with a regular expression, or with just native VBA. I am assuming from your code that the underscore character is also valid in the string.

To do this with native VBA, you need to build up the LIKE string since quantifiers are not included. Also using Option Compare Text makes the "like" action case insensitive.

Option Explicit
Option Compare Text
Function TestFormat(S As String) As Boolean
    'Sections
    Dim S1 As String, S2_4 As String, S5 As String
    Dim sLike As String
With WorksheetFunction
    S1 = .Rept("[A-Z0-9_]", 8)
    S2_4 = .Rept("[A-Z0-9_]", 4)
    S5 = .Rept("[A-Z0-9_]", 12)
    sLike = S1 & .Rept("-" & S2_4, 3) & "-" & S5
End With

TestFormat = S Like sLike

End Function

With regular expressions, the pattern is simpler to build, but the execution time may be longer, and that may make a difference if you are processing very large amounts of data.

Function TestFormatRegex(S As String) As Boolean
    Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .MultiLine = True
    .Pattern = "^\w{8}(?:-\w{4}){3}-\w{12}$"
    TestFormatRegex = .test(S)
End With
End Function

How to test for specific characters with regex in VBA

3 Answers3