VBA 6 : regex not recognizing complicated string

Question

I have this string "1X214X942,0SX" where each X represents a "non-breaking space" (a whitespace to be exact) with the ASCII code 160, and S represents a space character.

I am trying to recognize it with this regex:

(\d{1,3}\s(\d{3}\s)*\d{3}(\,\d{1,3})?|\d{1,3}\,\d{1,3})

but it doesn't work, as this whitespace is not recognized and the expression only catches 942,0.

I have tried to see if RegExr can catch the whole string and it can, ( http://gskinner.com/RegExr/?2v8ic) so there's something wrong with my VBA then ?

Please advise !

Here is my code :

Sub ChangeNumberFromFRformatToENformat()

Dim SectionText As String
Dim RegEx As Object, RegC As Object, RegM As Object
Dim i As Integer


Set RegEx = CreateObject("vbscript.regexp")
With RegEx
    .Global = True
    .MultiLine = False
    .Pattern = "(\d{1,3}\s(\d{3}\s)*\d{3}(\,\d{1,3})?|\d{1,3}\,\d{1,3})"
End With

For i = 1 To ActiveDocument.Sections.Count()

    SectionText = ActiveDocument.Sections(i).Range.Text

    If RegEx.test(SectionText) Then
        Set RegC = RegEx.Execute(SectionText)

        For Each RegM In RegC

            Call ChangeThousandAndDecimalSeparator(RegM.Value)

        Next 'For Each RegM In RegC

        Set RegC = Nothing
        Set RegM = Nothing

    End If

Next 'For i = 6 To ActiveDocument.Sections.Count()

Set RegEx = Nothing

End Sub

score 4 · Answer 1 · answered Dec 14 '11 at 08:57

4

The sequence \s doesn't match the non-breaking space, however there are ways you can do it. The atoms you want to consider using in the regexp are \xnn or \nnn -- these match a character by it's hexadecimal value or it's octal value.

Thus to match a non-breaking space of ASCII 160, specify one of \xA0 or \240 instead of \s.

answered Dec 14 '11 at 08:57

Chris J

30,688
6
69
111

thx you so so much Chris, especially for the explanation. worked as a charm! – Radu Puspana Dec 14 '11 at 09:44
Radu, please accept the answer by selecting the arrow on the upper left. +1 – Gaijinhunter Dec 14 '11 at 11:51
@ admin : hello admin, it seems that both answers are correct, however I have only tried out the first one. what should I do ? – Radu Puspana Dec 14 '11 at 15:26

score 2 · Answer 2 · answered Dec 14 '11 at 08:56

The problem is, that \s does not contain the non-breaking space in the VBScript.RegExp implementation.

Try this instead:

With RegEx
    .Global = True
    .MultiLine = False
    .Pattern = "(\d{1,3}X(\d{3}X)*\d{3}(,\d{1,3})?|\d{1,3},\d{1,3})"
    .Pattern = Replace(.Pattern, "X", "[\s" & Chr(160) & "]")
End With

Regular expression patterns match literal characters you put into them, even the non-breaking space. The only way to add a non-breaking space to a VB string is by creating it with Chr().

thank you very much Tomalak ! I didn't get the chance to test this cause my business rules have changed. Never the less your method is more handy, more easy to use than Chris's — Radu Puspana, Dec 14 '11 at 14:24

VBA 6 : regex not recognizing complicated string

2 Answers2

Linked