Getting only digits in a string using regex in vba

Question

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.

zedfoxus · Accepted Answer · 2015-05-20 05:04:29Z

2

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

answered May 20, 2015 at 5:04

zedfoxus

37.4k5 gold badges68 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

lakshmen Over a year ago

jus a qn: when i tried "[\d+]", it was returning all the letters. Why is that so? i thought that \d looks for digits in a string right?

zedfoxus Over a year ago

\d+ means one or more digits. So the RegEx stripped off everything except the numbers 0-9 and gave you 650000000000593000000. With \d.:+ we asked RegEx to keep 0-9, dot and colon but strip out everything else.

lakshmen Over a year ago

but u have entered "[^\d.:+]" not [\d.:+]. so where is the difference?

zedfoxus Over a year ago

Correct usage is [^\d.:+]. I wrote \d.:+ in a hurry. [^\d.:+]s beauty lies in the ^ part of the RegEx. The caret symbol inverts the selection; meaning, it match all characters that are not a digit, dot or colon. That's how you can strip characters you don't want.

lakshmen Over a year ago

i thought it was re.Execute, didn't know it is re.Replace. Thanks a lot.

Collectives™ on Stack Overflow

Getting only digits in a string using regex in vba

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related