CODEWITHSUNDEEP
regexexcelvba
poeschlorn
poeschlorn

Reputation: 12440

VBA Regex issue

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

Upvotes: 2

Views: 792

Answers (3)

Gaijinhunter
Gaijinhunter

Reputation: 14685

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+")

  • The ^ marks the start of the sentence
  • The \w marks a single alphabetic character (a-zA-Z)
  • The \d+ marks one or more numeric characters[0-9]

I hope this helps.

Upvotes: 0

Alex K.
Alex K.

Reputation: 175748

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)

Upvotes: 5

murgatroid99
murgatroid99

Reputation: 20232

I don't know VBA, but the regex [a-zA-Z][0-9]* might be able to match what you want.

Upvotes: 0

Related Questions