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

Amen Jlili · Accepted Answer · 2015-02-27 19:15:23Z

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:

Str is the string to extract numbers from
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)

answered Feb 27, 2015 at 19:15

Amen Jlili

1,9444 gold badges28 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user4039065 Over a year ago

Funny but I count at least 6 lines (doubling the declaration and assignment of regex into a single line still counts as two) plus the fact that you didn't dim matches or set matches and regex to Nothing before exiting. :)

Amen Jlili Over a year ago

True, unknown person...

Kyle · Accepted Answer · 2015-02-27 18:28:37Z

3

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

answered Feb 27, 2015 at 18:28

Kyle

2,5452 gold badges19 silver badges33 bronze badges

Comments

Gary's Student · Accepted Answer · 2015-02-27 18:40:43Z

2

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

enter image description here

answered Feb 27, 2015 at 18:40

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Comments

user4039065 · Accepted Answer · 2015-02-27 18:49:52Z

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

VBA text parsing Split

The formula in B1 is,

=days_supply(A1)

The formula in C1 is,

=days_supply(A1,2)

answered Feb 27, 2015 at 18:49

user4039065

Collectives™ on Stack Overflow

Extract number from string in VBA

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related