VBA: a regular expression for highlighting specific parts of a string

Question

VBA: a regular expression for highlighting specific parts of a string

Please tell me how for Excel VBA should look like a regular expression to select from the text of the form

12345.12345TEXT12345

I.e. (number dot number text number (optional))

The number before the text and the text itself

For example:

строка: 4320.17AB32

найдено: 4320.17 and AB

Or

строка: 50.1702ZZA

найдено: 50.1702 and ZZA

Compose:

Dim regEx As New RegExp
With regEx
    .Global = True
    .MultiLine = True
    .IgnoreCase = False
    .Pattern = "[0-9]*\.?[0-9]*[a-zA-Z]*[0-9]*$"
End With

Set matches = regEx.Execute(text)

But it seems that I do not understand anything in regular expressions at all : (because it finds only the full string

1

excel регулярные-выражения vba

Author: Zhihar, 2021-02-03

Source

1 answers

score 0 · Answer 1

Use exciting groups:

\b(\d+(?:\.\d+)?)([A-Za-z]*)

See example of how the regular expression works. Details:

\b - word boundary
(\d+(?:\.\d+)?) - exciting submask №1 (.Submatches(0)): one or more digits, followed by an optional sequence: dot + one or more digits
([A-Za-z]*) - exciting submask №2 (.Submatches(1)): zero or more ASCII letters.

Sample code in VBA:

Dim regEx As regExp, Matches As Object, m As Object
Dim str As String

Dim coll As Collection

str = "текст: 4320.17AB32"
Set regEx = New regExp
regEx.Pattern = "\b(\d+(?:\.\d+)?)([A-Za-z]*)"

Set Matches = regEx.Execute(str)

If Matches.count > 0 Then
 For Each m In Matches
  Debug.Print "Найдено: " & m.SubMatches(0) & " и " & m.SubMatches(1)
 Next
End If

Result: Найдено: 4320.17 и AB