has VBA any good mechanism for checking, if the content of a given Excel Cell matches a specific regex?
In my case i want to know, if some cell has the format
m
m2
m1234
In fact, there's just one defined letter at the beginning, followed by a not specified amount of numbers.
How do I put this into an If-Else construct?
If Doc.Cells(1,1).Value ..... ???
greets, poeschlorn
You can get at the VBScript RegExp objects via Tools->References & adding "Microsoft VBScript Regular Expressions 5.5"
Alternatively a quick way to do it, if you don't need to check for a subsequent letter as in `m1234X1 is:
if Doc.Cells(1,1).Value like "[a-zA-Z]#*" then ...
(This doesn't require a reference to anything)
I don't know VBA, but the regex [a-zA-Z][0-9]* might be able to match what you want.
Here is my RegexContains function. Pass it the cell and the pattern and it will return TRUE or FALSE if it contains it or not.
Function RegexContains(ByVal find_in As String, _
ByVal find_what As String, _
Optional IgnoreCase As Boolean = False) As Boolean
Application.ScreenUpdating = False
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = find_what
RE.IgnoreCase = IgnoreCase
RE.Global = True
RegexContains = RE.Test(find_in)
Application.ScreenUpdating = True
End Function
Now, I'm not sure exactly what you want to find in your example, but if you want to know if the cell contains a single letter followed by one or more letters, then you would use (assuming the cell is A1): =RegexContains(A1, "^\w\d+")
I hope this helps.
