How do I get my regex to ignore decimal-separator if it is not followed by a number?

Question

I am trying to come up with a function to capture a number from a string. What I came up with on my own is currently used in this code:

Function ArrayOfCleanedString(strIn As String) As Variant
  Dim objRegex As Object, objAllMatches As Object, sAllMatches() As String, v As Variant, i As Long
  Set objRegex = CreateObject("vbscript.regexp")

  objRegex.Global = True
  objRegex.IgnoreCase = False
  'objRegex.Pattern = "^(?:(?i)(?:[+-]?)(?:(?=[0123456789]|[.])(?:[0123456789]*)(?:(?:[.])(?:[0123456789]{0,}))?)(?:(?:[E])(?:(?:[+-]?)(?:[0123456789]+))|))$"
  'objRegex.Pattern = "^-?(?:(?:0|[1-9][0-9]*)(?:,[0-9]+)?|[1-9][0-9]{1,2}(?:,[0-9]{3})+)$"
  objRegex.Pattern = "\-?\d+[" & Application.DecimalSeparator & "\.]?\d*"

  Set objAllMatches = objRegex.Execute(strIn)

  If objAllMatches.Count <> 0 Then
    ReDim sAllMatches(1 To objAllMatches.Count)
    For Each v In objAllMatches
      i = i + 1
      sAllMatches(i) = v
    Next
    ArrayOfCleanedString = sAllMatches
  Else
    ArrayOfCleanedString = Array()
  End If
End Function

Sub test()
  Dim v As Variant, v2 As Variant

  v2 = ArrayOfCleanedString("1abc-10.1-1abx1,1o1.")
  'v2 = ArrayOfCleanedString("")
  If Not IsArrayEmpty(v2) Then
    For Each v In v2
      Debug.Print CStr(v)
    Next
  End If
End Sub

This code does however have a problem in that it captures a punctuation / comma at the end of the number, even if there are no digits after it.

I did some searching, and found the two other patterns I've tried in this post, but as you can probably guess they didn't work in VBA :)

I'd try modifying them, but considering that I don't really understand them that is somewhat tricky.

So what I am asking is; is there any simple way to strip the punctuation mark / comma from the end of the match, if it isn't followed by a number?

Alternately, is there any way to translate any of the other two patterns to VBA's brand of regex?

Is there any other obvious flaws in my method? I am quite new to this, so I do a lot of trial and error :P

Oh, and in case any of you wonder about the isEmptyArray method in my code, it is copied from Chip Pearson's page of functions for VBA-arrays.

Answer 1

You can use the posted regex and simply remove trailing "." from elements of the array before returning the array:

Function ArrayOfCleanedString(strIn As String) As Variant
    Dim objRegex As Object, objAllMatches As Object, sAllMatches() As String, v As Variant, i As Long
    Set objRegex = CreateObject("vbscript.regexp")

    objRegex.Global = True
    objRegex.IgnoreCase = False
      'objRegex.Pattern = "^(?:(?i)(?:[+-]?)(?:(?=[0123456789]|[.])(?:[0123456789]*)(?:(?:[.])(?:[0123456789]{0,}))?)(?:(?:[E])(?:(?:[+-]?)(?:[0123456789]+))|))$"
      'objRegex.Pattern = "^-?(?:(?:0|[1-9][0-9]*)(?:,[0-9]+)?|[1-9][0-9]{1,2}(?:,[0-9]{3})+)$"
    objRegex.Pattern = "\-?\d+[" & Application.DecimalSeparator & "\.]?\d*"

  Set objAllMatches = objRegex.Execute(strIn)

  If objAllMatches.Count <> 0 Then
    ReDim sAllMatches(1 To objAllMatches.Count)
    For Each v In objAllMatches
      i = i + 1
      sAllMatches(i) = v
      If Right(sAllMatches(i), 1) = "." Then
        sAllMatches(i) = Left(sAllMatches(i), Len(sAllMatches(i)) - 1)
      End If
    Next
    ArrayOfCleanedString = sAllMatches
  Else
    ArrayOfCleanedString = Array()
  End If

End Function