VBA - Extract numbers from string before specific phrases

[gabbi0712]

Hi,

I'm a beginner in VBA and I'm trying to extract different numbers which will always occur after a specific phrase in the 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.
Ex : the client has bought 65 gazons en rouleaux, 1 engrais entretien, 1 engrais starter
65x Gazon en rouleaux(id:1) | 1x Engrais d'entretien Sac jusqu'à 200m²(id:5) | 1x Engrais starter Sac jusqu'à 80m²(id:4)

I would like to have a column for each product and only the quantity.

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.
For instance, we can have cells like below :
Only 2 products bought
34x Gazon en rouleaux(id:1) | 1x Engrais starter Sac jusqu'à 80m²(id:4)

Only 1 product bought
350x Gazon en rouleaux(id:1)

I've tried to use several macro found on the web but they didn't match my exact request and I was unable to customize them as I'm too beginner.

Below one of the macro that seemed to address my need.

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


Thank you very much for your help.

Gabbi

 

[Micron]

Best to copy & paste examples of data that covers the range of possibilities and expected results.
If the string is "The cost is $100.50 USD for 50 units" what do you want - "100" or "100 50" or "10050" or "100.50 50" or something else?
Then what about currency signs and decimals in numbers? You want "$100 50" or "$100.50 50?

You don't necessarily have to find a string - you can get just the numbers in another way. Your example would not return 100 from the cost and 50 for the count.

 

[gabbi0712]

Hi,

The data are as below. As you can see, the string is a variable list of products bought by the customer

Product_list

16x Gazon en rouleaux(id:1) | 1x Engrais starter Sac jusqu'à 80m²(id:4) | 2x Sedum cassette (id:5)

28x Gazon en rouleaux(id:1) | 1x Engrais starter Sac jusqu'à 80m²(id:4) | 3x Sedum tapis (id:6)

200x Gazon en rouleaux(id:1) | 1x Engrais d'entretien Sac jusqu'à 200m²(id:5) | 3x Engrais starter Sac jusqu'à 80m²(id:4)

350x Gazon en rouleaux(id:1)

I'd like to get :

Quantity Gazon	Quantity Engrais starter	Quantity Engrais d'entretien	Quantity Sedum cassette	Quantity Sedum tapis
16​	1​		2​
28​	1​			6​
200​	3​	1​
350​

Thanks a lot,

 

[Micron]

OK, I was prepared to code for having the values in one cell based on the code you provided. If you can have
16x Gazon en rouleaux(id:1) | 1x Engrais starter Sac jusqu'à 80m²(id:4) | 2x Sedum cassette (id:5)
or maybe
1x Engrais starter Sac jusqu'à 80m²(id:4) | 2x Sedum cassette (id:5) | 16x Gazon en rouleaux(id:1)
or even
16x Gazon en rouleaux(id:1) | 2x Sedum cassette (id:5)

then order means nothing, and each value related to an item needs to be slotted into a column for that item. Maybe the order is always the same but when an item has no order quantity and is somewhere in the middle of a string is there is a blank for that value as in
16x Gazon en rouleaux(id:1) | | 2x Sedum cassette (id:5)

so that the order is always the same, but you didn't cover that. Doesn't really matter I guess, because I think having to slot them into particular columns would be too time consuming for me but it might be useful information for someone else. However, if just having the numbers would get you started then consider what I have already written:

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 + 1), " ")

Debug.Print Mid(sExpression, NumStart, NumLen)

End Sub

Unfortunately that was based on the code you provided as your example, which you said seemed to address your need but which now seems irrelevant, especially the "price of" part. So mine probably won't work for you because this
3x Engrais starter Sac jusqu'à 80m²(id:4)
comes out as 3 804

I hope someone can chime in and start from scratch.
EDIT - Maybe a better approach would be to attack from the other way; i.e. use your column header values to zero in on the items.

 

[gabbi0712]

Hi,

Thanks a lot Micron.

Yes the code I shared is actually misleading and irrelevant for the required need, sorry for that.

Someone provided me with thé following solution, which works even if it needs some finetuning.

Sub Unpivot()
     Dim aA, Dict, i, j
     Set Dict = CreateObject("scripting.dictionary")
     With Sheets("Feuil1")
          aA = .Range("A1").CurrentRegion.Resize(, 1).Value2
          For i = 2 To UBound(aA)
               sp = Split(aA(i, 1), "|")
               For j = 0 To UBound(sp)
                    sp1 = Split(Trim(sp(j)), " ", 2)
                    If StrComp(Right(sp1(0), 1), "x", 1) = 0 And UBound(sp1) = 1 Then
                         Dict.Add Dict.Count, Array(i, sp1(0), sp1(1))
                    Else
                         Dict.Add Dict.Count, Array(i, "", sp(j))
                    End If
               Next
          Next

          With .Range("F1")
               .Resize(, 3).EntireColumn.ClearContents
               .Resize(Dict.Count, 3).Value = Application.Index(Dict.items, 0, 0)
             .Resize(, 3).EntireColumn.AutoFit
          End With
     End With
End Sub