Regex to extract numbers from a String in VBA

Question

How can I extract the numbers from col A and print in into col B.

I am using the below regex function, it print all the numbers with a space between them.

How can I get the initial set of numbers and skip the remaining ones.

Docetaxel Injection 160MG/16ML prints 160 16. I need to print only 160.

Private Sub splitUpRegexPattern()
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1:A10")

    For Each C In Myrange
        strPattern = "\D+"

        If strPattern <> "" Then
            strInput = C.Value
            strReplace = "$1"

            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With

            If regEx.test(strInput) Then
                C.Offset(0, 1) = regEx.Replace(strInput, " ")
            Else
                C.Offset(0, 1) = "(Not matched)"
            End If
        End If
    Next
End Sub

Instead of using Regex, can you just search for a space, and use =left? — Selkie
– Selkie, Commented Jan 22, 2018 at 23:32
Probably not - I was under the impression from the example you gave that your data is just numbers morenumbers - is this incorrect? — Selkie
– Selkie, Commented Jan 22, 2018 at 23:36
With the regex function it is extracting just numbers. E.g. Docetaxel Injection 160MG/16ML prints 160 16. I am trying to print just the digits before the space, like just 160 and not 160 16 in the new column — MC12
– MC12, Commented Jan 22, 2018 at 23:42

Tim Williams · Accepted Answer · 2018-01-23 00:30:06Z

2

This should work (pattern allows for decimals but not very robustly so):

Sub splitUpRegexPattern()

    Dim re As Object, c As Range
    Dim allMatches

    Set re = CreateObject("VBScript.RegExp")
    re.Pattern = "([\d+\.]+)"
    re.IgnoreCase = True
    re.Global = True

    For Each c In ActiveSheet.Range("A1:A10").Cells
        Set allMatches = re.Execute(c.Value)
        If allMatches.Count > 0 Then
            c.Offset(0, 1).Value = allMatches(0)
        Else
            c.Offset(0, 1).Value = "(Not matched)"
        End If
    Next c

End Sub

answered Jan 23, 2018 at 0:30

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

5 Comments

MC12 Over a year ago

This solution does not print the numbers, everything except the numbers, like PRECEDEX 200 mcg 2 mL FTV will get printed as PRECEDEX mcg mL FTV

Tim Williams Over a year ago

So what you're saying is you didn't even try it?

MC12 Over a year ago

Below is what I did to print just the first set

If regEx.test(strInput) Then                 C.Offset(0, 1) = regEx.Exceute(strInput)(0)             Else                 C.Offset(0, 1) = "(Not matched)"             End If

MC12 Over a year ago

Yes, it stops when it finds the first " . " like in Docetaxel I.V. Infusion 120mg/12mL Hospira it prints . and not 120

Tim Williams Over a year ago

You can just remove the \. if you don't care about decimals. Or find a more-robust pattern...

0m3r · Accepted Answer · 2018-01-23 01:29:02Z

1

If its always 3 digits then use \s\d{3} https://regex101.com/r/lEc4mN/1

Option Explicit
Private Sub splitUpRegexPattern()
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim Myrange As Range
    Dim C As Range
    Dim Matches As Variant

    Set Myrange = ActiveSheet.Range("A1:A10")

    For Each C In Myrange
        strPattern = "\s\d{3}"

        If strPattern <> "" Then
            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
                 Set Matches = .Execute(C.Value)
            End With

            If Matches.Count > 0 Then
                Debug.Print Matches(0)
                C.Offset(0, 1) = Matches(0)
            Else
                C.Offset(0, 1) = "(Not matched)"
                Debug.Print "Not Found "
            End If

        End If
    Next
End Sub

answered Jan 23, 2018 at 1:29

0m3r

12.5k15 gold badges40 silver badges77 bronze badges

2 Comments

MC12 Over a year ago

I am trying to extract the first set of numbers till the space. PRECEDEX 200 mcg 2 mL FTV should print only 200 and not 200 2.

0m3r Over a year ago

@user9230639 it should work- also try to C.Offset(0, 1) = Trim(Matches(0))

Collectives™ on Stack Overflow

Regex to extract numbers from a String in VBA

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related