CODEWITHSUNDEEP
regexvbaexcel
Ibo
Ibo

Reputation: 4309

VBA - Extracting numbers only using regular expressions

I have the below code that keeps the numbers in a string. It keeps the points regardless, but I want to keep the points IF THEY ARE FOLLOWED BY A DIGIT. So the function should give me the following results. What should I change in the regular expression pattern to get such results?

Input | Output

. . 0.236 | 0.236

bfbv0.011 | 0.011

. ..11 | 0.11

. rty12.45dt | 12.45

qw-23.25 | -23.25

was 12.52. | 12.52

will be +336 | 336

Code:

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 = "[^0-9.-]+"
    s2 = re.Replace(s, vbNullString)

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

Upvotes: 0

Views: 6566

Answers (1)

A.S.H
A.S.H

Reputation: 29332

-?\d*\.?\d+ achieves all your needs, but some later processing is needed to insert the leading zero when necessary. The pattern includes an optional leading minus sign and an optional dot but if there's a dot it must be followed by some digits or it will be ignored.

  Public Function NumericOnly(s As String) As String
      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*\.?\d+"
       End If

      If re.Test(s) Then
          NumericOnly = re.Execute(s)(0)
          If Left(NumericOnly, 1) = "." Then NumericOnly = "0" & NumericOnly ' <-- to add leading 0 if necessary
          If Left(NumericOnly, 2) = "-." Then NumericOnly = "-0." & Mid(NumericOnly, 3) ' <-- to add leading 0 if necessary
      End If
  End Function

  Sub Test()
    Dim s
    For Each s In Split("abc, 123, 123.0, 0.00, -.01, bfbv0.011,. ..11,. rty12.45dt,qw-23.25,was 12.52. ,will be +336", ",")
      Debug.Print s, vbTab, NumericOnly(CStr(s))
    Next
  End Sub

Output

 abc            
 123                123
 123.0              123.0
 0.00               0.00
 -.01               -0.01
 bfbv0.011          0.011
. ..11              0.11
. rty12.45dt        12.45
qw-23.25            -23.25
was 12.52.           12.52
will be +336         336

Upvotes: 5

Related Questions