Excel VBA如何反向提取子字符串？提取数量与单位的问题

方法1：使用正则表达式（推荐） #

正则可以直接匹配连续数字+kt/kb的模式，一步到位提取目标内容，不受数字长度限制，代码简洁易维护：

Sub ExtractAmtUnit()
    Dim regex As Object
    Dim matches As Object
    Dim exampleTexts As Variant
    Dim i As Integer
    
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "(\d+)(kt|kb)" ' 匹配数字+目标单位的模式
    regex.Global = False
    
    ' 示例文本数组
    exampleTexts = Array( _
        "Peter sell 1kt Gold to James", _
        "Lester sell 20kt Silver to Maya", _
        "Aplh sell 300kb of Liquid Nitrogen to Liz", _
        "Beth sell to Carl 40kb of Palm Oil" _
    )
    
    ' 输出表头
    Debug.Print "Amt" & vbTab & "Unit"
    
    For i = LBound(exampleTexts) To UBound(exampleTexts)
        Set matches = regex.Execute(exampleTexts(i))
        If matches.Count > 0 Then
            Debug.Print matches(0).SubMatches(0) & vbTab & matches(0).SubMatches(1)
        End If
    Next i
End Sub

方法2：从单位位置反向遍历找数字起始点 #

如果不想用正则，可通过遍历确定数字的起始位置：找到kt/kb的位置后，往前遍历直到遇到非数字字符，以此划分数量与单位的边界：

Sub ExtractAmtUnitWithoutRegex()
    Dim exampleTexts As Variant
    Dim i As Integer
    Dim unitPos As Integer
    Dim amtStartPos As Integer
    Dim amt As String
    Dim unit As String
    
    exampleTexts = Array( _
        "Peter sell 1kt Gold to James", _
        "Lester sell 20kt Silver to Maya", _
        "Aplh sell 300kb of Liquid Nitrogen to Liz", _
        "Beth sell to Carl 40kb of Palm Oil" _
    )
    
    Debug.Print "Amt" & vbTab & "Unit"
    
    For i = LBound(exampleTexts) To UBound(exampleTexts)
        ' 优先查找kb，找不到再找kt
        unitPos = InStr(exampleTexts(i), "kb")
        If unitPos = 0 Then
            unitPos = InStr(exampleTexts(i), "kt")
        End If
        
        If unitPos > 0 Then
            unit = Mid(exampleTexts(i), unitPos, 2) ' 提取单位
            ' 反向遍历找第一个非数字字符，确定数量起始位置
            amtStartPos = unitPos - 1
            Do While amtStartPos >= 1 And IsNumeric(Mid(exampleTexts(i), amtStartPos, 1))
                amtStartPos = amtStartPos - 1
            Loop
            amtStartPos = amtStartPos + 1 ' 回到第一个数字的位置
            amt = Mid(exampleTexts(i), amtStartPos, unitPos - amtStartPos)
            Debug.Print amt & vbTab & unit
        End If
    Next i
End Sub

你之前代码的问题说明 #

• Mid(examples, pos1, -1) 错误：Mid的第三个参数是提取的长度，必须为正整数，传入负数会直接报错。
• Mid(examples, pos1 - 2, 1) 不可靠：仅能提取固定位置的单个字符，当数字长度为1位（如1kt）或3位（如300kb）时，该位置并非数字，因此会得到空值或错误字符。

关于VBA的负索引问题 #

VBA的Mid、Left、Right均不支持负索引，没有类似Java Substring的负索引用法。如果需要反向提取，要么采用上述遍历方案，要么用StrReverse反转字符串后再提取，但遍历或正则的方案比StrReverse更直观高效。

内容的提问来源于stack exchange，提问作者user2741620