Searching a string for numbers including decimals in VBA

Question

So I'm working on a project that has inputs from a fairly clunky database that I have zero control over what type of data it gives me. It basically gives me a string that has numbers in it including decimals.

"take 0.5 Tab by mouth 2 times daily."

Whenever it says tab I want to grab the number before tab and convert it to double format. I know how to use cdbl to convert it once I have the string "0.5" but how I get just that string is kind of difficult since InStr only searches left to right. My thought was to use InStr to find the space before the number that comes before the word "tab" but I'm having trouble figuring out how to code it. Any suggestions?

Answer 1

InStrRev searches right to left. Alternatively, you can use StrReverse and work with the output, but I would use VBScript.Regexp:

Dim text As String
text = "take 0.5 Tab by mouth 2 times daily"

Dim regex As Object
Set regex = CreateObject("VBScript.Regexp")

regex.Global = True
regex.Pattern = "[\d\.]+(?=\sTab)"

Dim test As Object
Set test = regex.Execute(text)
MsgBox (test(0).Value)