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.
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