Extract number from string in VBA

Question

I have cells in vba that contain strings like this:

QUANTITY SUPPLY <= DAYS SUPPLY|30 IN 23 DAYS

I send these strings through two functions that just picks out the two numbers and parses them into the appropriate cells and just scraps the rest. The function that picks out the days number (23) is working fine but the function that picks out the 30 is not. I have been testing it and it seems to be parsing out the 30 as well as the whole string before it when all I want is the 30. In the case of the above string, it is returning "QUANTITY SUPPLY <= DAYS SUPPLY|30" when all I want it to return is the 30. I have looked at the function and cannot find the issue. Any help with this issue would be greatly appreciated!

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

Answer 1

You might pass an optional parameter in to distinguish which number you want to pull.

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

    

The formula in B1 is,

=days_supply(A1)

The formula in C1 is,

=days_supply(A1,2)

Answer 2

Have you considered using the "Split" function in VBA? If it is always pipe delimited, you could try:

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

Answer 3

This will extract the first number in a string:

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

Answer 4

This is by far the shortest (5 lines) function to extract the numbers!

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

Parameters:

1. Str is the string to extract numbers from
2. Occur is the occurrence of that number (it's 0-based so the first number will have the occurence of 0 not 1 and so on)