VBA: extract number from string after specific phrase

[pvr928]

Hi

I am trying to extract a number which will always occur after a specific phrase in a string.

As the length of the number will vary, ideally I want the code to simply search for the space occurring after the number and then determine that the number must lie between the end of the specific phrase and the space.

I tried to use RegExp, without success.

I am reverting to Mid, Instr and Len:

Sub GetPrice()

Dim sExpression As String
Dim sPhrase As String

sExpression = "19 apples with price of $0.30 and use by date of 31 July 2016"
sPhrase = "price of"

Debug.Print Mid(sExpression, InStr(sExpression, sPhrase) + Len(sPhrase) + 1, 5)

End Sub

The '5' is want I want the code to automatically detect.

Any help as always greatly appreciated.

Cheers

pvr928

 

[CalcSux78]

how about this?:

Sub FindCurrency()
Dim str As String
Dim i As Integer
Dim j As Integer

str = "19 apples with price of $0.30 and use by date of 31 July 2016"
i = InStr(1, str, "$")
j = InStr(i, str, " ") - i
Debug.Print Format(Mid(str, i, j), "Currency")

End Sub

 

[Ron Coderre]

Try something like this:

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

Is that something you can work with?

 

[pvr928]

Try something like this:

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

Is that something you can work with?

Brilliant CalcSux78 and Ron

You've both given me answers that are so helpful. Although yours approaches it from a different angle, CalcSux78, it might be more important to search for the $ sign rather than the preceding phrase now that I think about it.

Cheers guys

pvr928