在VBA中从字符串中提取数字

Question

我在vba中有一些包含以下字符串的单元格：

QUANTITY SUPPLY <= DAYS SUPPLY|30 IN 23 DAYS

我通过两个函数将这些字符串传递，这两个函数只选择了两个数字并将它们解析到适当的单元格中，并且删除了其余部分。选取天数的函数（23）正常工作，但选取30的函数则不行。我一直在测试它，似乎它把整个30和之前的所有字符串都解析出来了，而我想要的仅仅是30。对于上述字符串，它返回了“QUANTITY SUPPLY <= DAYS SUPPLY | 30”，而我希望它只返回30。我查看了函数，但无法找到问题。非常感谢您对此问题的任何帮助！

Public Function extractQLlMax(cellRow, cellColumn) As String
    qlm = Cells(cellRow, cellColumn).Value
    extractQLlMax = qlm
    If extractQLinfoBool = "Yes" And Not InStr(1, qlm, "IN") = 0 Then
        If InStr(1, qlm, "QUANTITY SUPPLY") > 0 Then
        pipeIndex = InStr(1, qlm, "|")
        inIndex = InStr(1, qlm, "IN")
        extractQLlMax = Mid(qlm, pipeIndex, inIndex  - pipeIndex)
        End If
        inIndex = InStr(1, qlm, "IN")
        extractQLlMax = Mid(qlm, 1, inIndex - 2)
    ElseIf extractQLinfoBool = "Yes" And Not InStr(1, qlm, "FILL") = 0 Then
        perIndex = InStr(1, qlm, "PER")
        extractQLlMax = Mid(qlm, 1, perIndex - 2)
    End If
End Function

- Philip

Answer 1

这是迄今为止提取数字最短的（仅有5行）函数！

Function GetNumbers(str As String, Occur As Long) As Long
Dim regex As Object: Set regex = CreateObject("vbscript.RegExp")
regex.Pattern = "(\d+)"
Regex.Global = True
Set matches = regex.Execute(str)
GetNumbers = matches(Occur)
End Function

参数：

1. Str 是需要从中提取数字的字符串
2. Occur 是该数字在字符串中出现的次数（从0开始，因此第一个数字的出现次数为0，以此类推）

- Amen Jlili

---

有趣的是，我至少数了6行代码（即使将声明和正则表达式赋值合并为一行仍然算两行），而且你在退出之前没有将matches和regex设置为Nothing或者未定义。 :) - user4039065

真的，未知的人... - Amen Jlili

Answer 2

您是否考虑过在VBA中使用“Split”函数？如果它总是以管道分隔符分隔，您可以尝试：

Public Function extractQLlMax(cellRow, cellColumn) As String
    Dim X as Variant
    qlm = Cells(cellRow, cellColumn).Value
    extractQLlMax = qlm

    If extractQLinfoBool = "Yes" And Not InStr(1, qlm, "IN") = 0 Then
        If InStr(1, qlm, "QUANTITY SUPPLY") > 0 Then
        x = Split(qlm,"|")
        extractQLlMax = X(ubound(x))
    ElseIf extractQLinfoBool = "Yes" And Not InStr(1, qlm, "FILL") = 0 Then
        perIndex = InStr(1, qlm, "PER")
        extractQLlMax = Mid(qlm, 1, perIndex - 2)
    End If
End Function

- Kyle

Answer 3

这会提取字符串中的第一个数字：

Public Function GetNumber(s As String) As Long
    Dim b As Boolean, i As Long, t As String
    b = False
    t = ""
    For i = 1 To Len(s)
        If IsNumeric(Mid(s, i, 1)) Then
            b = True
            t = t & Mid(s, i, 1)
        Else
            If b Then
                GetNumber = CLng(t)
                Exit Function
            End If
        End If
    Next i
End Function

- Gary's Student

Answer 4

您可以传入一个可选参数来区分您想要提取的数字。

Public Function days_supply(blurb As String, Optional i As Long = 1)
    Dim sTMP As String
    sTMP = Trim(Split(blurb, "|")(1))

    If i = 1 Then
        days_supply = CLng(Trim(Left(Replace(sTMP, " ", Space(99)), 99)))
    Else
        sTMP = Trim(Mid(sTMP, InStr(1, LCase(sTMP), " in ", vbTextCompare) + 4, 9))
        days_supply = CLng(Trim(Left(Replace(sTMP, " ", Space(99)), 99)))
    End If
End Function

    

在B1单元格中的公式为，

=days_supply(A1)

C1单元格中的公式为，

=days_supply(A1,2)

- user4039065