RegEx从字符串中提取第一组数字

时间:2022-09-13 11:06:48

I am trying to extract the first set of digits only with regex function from col A in Vba.

我正在尝试从Vba中的col A中提取第一组带regex函数的数字。

PRECEDEX 200 mcg 2 mL FTV should print only 200. Currently my code prints all the numbers.

predex 200mcg2ml FTV只能打印200个。目前我的代码打印所有的数字。

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("E3:E1500")

    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

1 个解决方案

#1


2  

You should just use \d+ pattern, and use .Execute rather than .Replace method to actually extract the digits (you also need to use RegExp.Global=False to find only the first match).

您应该使用\d+模式,并使用. execute而不是. replace方法来实际提取数字(还需要使用RegExp)。全局=错误,只找到第一个匹配)。

Use

使用

Sub splitUpRegexPattern()
    Dim Regex As New regexp
    Dim strPattern As String
    Dim strInput As String
    Dim Myrange As Range
    Dim mtch As Object

    Set Myrange = ActiveSheet.Range("E3:E1500")

    For Each c In Myrange
        strPattern = "\d+"

        If strPattern <> "" Then
            strInput = c.Value


            With Regex
                .Global = False
                .MultiLine = True
                .IgnoreCase = False
                .pattern = strPattern

            End With

            If Regex.test(strInput) Then
                Set mtch = Regex.Execute(strInput)
                If mtch.Count > 0 Then
                    c.Offset(0, 1) = mtch.Item(0).Value
                End If
            Else
                c.Offset(0, 1) = "(Not matched)"
            End If
        End If
    Next
End Sub

Here, Set mtch = Regex.Execute(strInput) tries to find the match and if a match is found (If mtch.Count > 0), the value (mtch.Item(0).Value) is added to the next column on the right.

在这里,设置mtch = Regex.Execute(strInput)尝试查找匹配项,如果找到了匹配项(如果找到了mtch)。计数>)),值(mtch.Item(0). value)被添加到右边的下一列。

#1


2  

You should just use \d+ pattern, and use .Execute rather than .Replace method to actually extract the digits (you also need to use RegExp.Global=False to find only the first match).

您应该使用\d+模式,并使用. execute而不是. replace方法来实际提取数字(还需要使用RegExp)。全局=错误,只找到第一个匹配)。

Use

使用

Sub splitUpRegexPattern()
    Dim Regex As New regexp
    Dim strPattern As String
    Dim strInput As String
    Dim Myrange As Range
    Dim mtch As Object

    Set Myrange = ActiveSheet.Range("E3:E1500")

    For Each c In Myrange
        strPattern = "\d+"

        If strPattern <> "" Then
            strInput = c.Value


            With Regex
                .Global = False
                .MultiLine = True
                .IgnoreCase = False
                .pattern = strPattern

            End With

            If Regex.test(strInput) Then
                Set mtch = Regex.Execute(strInput)
                If mtch.Count > 0 Then
                    c.Offset(0, 1) = mtch.Item(0).Value
                End If
            Else
                c.Offset(0, 1) = "(Not matched)"
            End If
        End If
    Next
End Sub

Here, Set mtch = Regex.Execute(strInput) tries to find the match and if a match is found (If mtch.Count > 0), the value (mtch.Item(0).Value) is added to the next column on the right.

在这里,设置mtch = Regex.Execute(strInput)尝试查找匹配项,如果找到了匹配项(如果找到了mtch)。计数>)),值(mtch.Item(0). value)被添加到右边的下一列。