CODEWITHSUNDEEP
regexexcelvba
shmnff
shmnff

Reputation: 739

Regex VBA: match specific substring

I have a followed string:

K9908098,G2342DF34324/234234323~234-234-234;R38724234+3D9083324T234Y 23E4234323

How to catch only substrings with letters and digits, e.g. K9908098 and 3D9083324T234Y in VBA? This pattern [A-Z\d]+ matches all substrings, and this ([A-Z]+\d+)+ doesn't grabs 3D9083324T234Y.

Upvotes: 2

Views: 3705

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627371

You may use

\b(?=\d*[A-Z])(?=[A-Z]*\d)[A-Z\d]+\b

See the regex demo.

Details

  • \b - a word boundary
  • (?=\d*[A-Z]) - (positive lookahead requiring that) there must be an uppercase ASCII letter after 0+ digits
  • (?=[A-Z]*\d) - there must be an ASCII digit after 0+ uppercase ASCII letters
  • [A-Z\d]+ - 1+ uppercase ASCII letters or digits
  • \b - a word boundary.

See the VBA demo:

Dim regex As Object, allMatches As Object, match As Object
Dim s As String

s = "K9908098,G2342DF34324/234234323~234-234-234;R38724234+3D9083324T234Y 23E4234323"

Set regex = CreateObject("vbscript.regexp")
With regex
    .Global = True
    .MultiLine = False
    .Pattern = "\b(?=\d*[A-Z])(?=[A-Z]*\d)[A-Z\d]+\b"
End With

Set allMatches = regex.Execute(s)
For Each match In allMatches
    Debug.Print match.Value
Next

Output:

K9908098
G2342DF34324
R38724234
3D9083324T234Y
23E4234323

Upvotes: 2

Related Questions