CODEWITHSUNDEEP
regexvba
lakshmen
lakshmen

Reputation: 29064

Getting only digits in a string using regex in vba

I have a string as such:

tempString = "65.00000000;ACCUMPOINTS;Double:0.0593000000;D"

And my output shld be "65.000000,0.0593000000" or at least give two separated values.

I am using regex to find the values in the string.

My code:

tempString = "65.00000000;ACCUMPOINTS;Double:0.0593000000;D"
temp = NumericOnly(tempString)

Public Function NumericOnly(s As String) As String
    Dim s2 As String
    Dim replace_hyphen As String
    replace_hyphen = " "
    Static re As VBScript_RegExp_55.RegExp
    If re Is Nothing Then Set re = New RegExp
    re.IgnoreCase = True
    re.Global = True
    re.Pattern = "[^\d+]" 'includes space, if you want to exclude space "[^0-9]"("-?\\d+");
    s2 = re.Replace(s, vbNullString)
    re.Pattern = "[^\d+]"
    NumericOnly = re.Replace(s2, replace_hyphen)
End Function

My output is like this:

"650000000000593000000"

How to go about doing this? Need some help.

Upvotes: 1

Views: 2663

Answers (1)

zedfoxus
zedfoxus

Reputation: 37049

Just did a minor change in your regex. Instead of just using [^\d+], now [^\d.:+] is being used to indicate that we would like one or more of digits, dots or colons. Then, colon is replaced with a comma to get the desired result.

Sub Test()
    Dim tempString As String
    tempString = "65.00000000;ACCUMPOINTS;Double:0.0593000000;D"
    temp = NumericOnly(tempString)
    MsgBox temp
End Sub

Public Function NumericOnly(s As String) As String
    Dim s2 As String
    Dim replace_hyphen As String
    replace_hyphen = " "
    Static re As VBScript_RegExp_55.RegExp
    If re Is Nothing Then Set re = New RegExp
    re.IgnoreCase = True
    re.Global = True

    re.Pattern = "[^\d.:+]"
    s2 = re.Replace(s, vbNullString)

    re.Pattern = "[^\d.:+]"

    NumericOnly = re.Replace(s2, replace_hyphen)
    NumericOnly = Replace(NumericOnly, ":", ",")
End Function

Upvotes: 2

Related Questions