正则表达式

示例

源码

    ' 提取中文（单元格内输入）=baseReg(A3,2)   提取数字（单元格内输入）=baseReg(A3,1)
    Function baseReg(text As String, t As Integer) As String

        Dim reg As Object
        Dim mc As Object

        Set reg = CreateObject("VBScript.RegExp")

        With reg
            .Global = True

            If t = 1 Then
                .Pattern = "\d+"
            Else
                .Pattern = "\D+"
            End If

            Set mc = .Execute(text)

            If mc.Count > 0 Then
                baseReg = mc(0)
            Else
                baseReg = ""
            End If

        End With

    End Function

    ' 提取数字求和 =sumReg(B14)
    Function sumReg(text As String) As Double
        Dim all As Double
        Dim reg As Object
        Dim mc As Object
        Dim item As Object

        Set reg = CreateObject("VBScript.RegExp")
        reg.Global = True
        reg.Pattern = "\d+(\.\d+)?"

        Set mc = reg.Execute(text)

        all = 0
        For Each item In mc
            all = all + CDbl(item.Value)
        Next item

        sumReg = all

    End Function

    ' 提取品牌价格销量  直接点提取按钮
    Function allReg()

        Dim reg As Object
        Dim rng As Range
        Dim item As Range
        Dim mc As Object
        Dim m As Object
        Dim row As Long

        Set reg = CreateObject("VBScript.RegExp")
        reg.Global = False
        reg.IgnoreCase = True
        reg.Pattern = "([a-zA-Z]+\d*[a-zA-Z]*)([\u4e00-\u9fa5]+)(\d+)元(\d+)"

        With Worksheets("数据")
            Set rng = .Range("H2", .Cells(.Rows.Count, "H").End(xlUp))
            row = 2

            For Each item In rng
                Set mc = reg.Execute(item.Value)

                If mc.Count > 0 Then
                    Set m = mc(0)
                    Debug.Print m.SubMatches(3)
                    .Range("I" & row).Value = m.SubMatches(0)
                    .Range("J" & row).Value = m.SubMatches(1)
                    .Range("K" & row).Value = m.SubMatches(2)
                    .Range("L" & row).Value = m.SubMatches(3)
                End If

                row = row + 1
            Next item
        End With

    End Function