Extract numbers that occur before specific phrases

Gabbi0712 · Sep 9th 2022

Hi,

I'm a beginner in VBA and I'm trying to extract different numbers which will always occur after a specific phrase in a string.

The string looks like below (but can vary according to the products that are bought). It's a list of products that has been bought by a client with quantities before.

I would like to extract the number before each product and put it in a specific column. The output would like below :

The length of the numbers will vary. The list of products as well. That's why I can't do a split and the macro needs to find the specific text in the string to extract the right number and place it inthe right column.

Code

Sub GetPrice()

Dim sExpression As String
Dim sPhrase As String
Dim LenPhrase As Long
Dim NumStart As Long
Dim NumLen As Long
sExpression = "19 apples with price of $0.30 and use by date of 31 July 2016"
sPhrase = "price of"
LenPhrase = Len(sPhrase)
NumStart = InStr(sExpression, sPhrase) + LenPhrase + 1
NumLen = InStr(Right(sExpression, Len(sExpression) - NumStart), " ")
Debug.Print Mid(sExpression, NumStart, NumLen)


End Sub

Display More

**KjBox** · Sep 10th 2022

Try this

Code

Sub ExtractQuantities()
    Dim x, y, z, i&, ii&
    x = Sheet1.Cells(1).CurrentRegion
    ReDim z(1 To UBound(x, 1), 1 To 3)
    z(1, 1) = "Quantity Gazon": z(1, 2) = "Quantity Engrais starter"
    z(1, 3) = "Quantity Engrais d'entretien"
    For i = 2 To UBound(x, 1)
        y = Split(x(i, 1), "|")
        For ii = 0 To UBound(y)
            If InStr(1, Split(y(ii), "x ")(1), "Gazon") > 0 Then
                z(i, 1) = Split(y(ii), "x ")(0)
            ElseIf InStr(1, Split(y(ii), "x ")(1), "starter") > 0 Then
                z(i, 2) = Split(y(ii), "x ")(0)
            ElseIf InStr(1, Split(y(ii), "x ")(1), "entretien") > 0 Then
                z(i, 3) = Split(y(ii), "x ")(0)
            End If
        Next
    Next
    With Sheet1
        .Columns(2).Resize(, 3).Clear
        .[b1].Resize(UBound(z, 1), 3) = z
        .Columns(2).Resize(, 3).AutoFit
        Application.Goto .[a1]
    End With
    
End Sub

Display More

Gabbi0712 · Sep 10th 2022

Hi KjBox,

Thanks a lot. I've tried, but i got the message "Object required". I'll try to solve this problem to be able to run the macro and I'll keep you updated.

Thanks again

**KjBox** · Sep 10th 2022

Which line of code is highlighted if you click "Debug" on the error message?

Gabbi0712 · Sep 10th 2022

Hi again,

My bad , as my excel is in French, I forgot to change Sheet1 in Feuil1.

The macro works perfectly, ton of thanks for your precious support !

**KjBox** · Sep 10th 2022

You're welcome

Extract numbers that occur before specific phrases

Gabbi0712 Sep 10th 2022

Participate now!

Share