Finding strings for numbers, including decimal numbers in VBA

Question

So, I am working on a project in which there is data from a rather awkward database, in which I have zero control over what type of data it gives me. This basically gives me a string that has numbers, including decimal numbers.

" take 0.5 tab. orally 2 times a day."

Whenever he says a tab, I want to grab the number in front of the tab and convert it to double format. I know how to use cdbl to convert it when I have the string “0.5”, but how I get only this string is difficult because InStr only searches from left to right. My thought was to use InStr to find the space before the number that precedes the word “tab”, but it's hard for me to figure out how to encode it. Any suggestions?

Answer 1

InStrRev searches from 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) 

Answer 2

Update using a tab as a compliance indicator

Assuming Tab is an appropriate indicator, you can do the following:

 Sub ExtractElement() ' column 4 and row 6 contains the text "take 0.5 Tab by mouth 2 times daily" s = Cells(6, 4).Value ' split text into array for each space sAr = Split(s, " ") ' iterate over each element of array For i = 0 To UBound(sAr) - 1 ' if the array element "Tab" is reached If sAr(i) = "Tab" Then ' write the previous array element into the next column Cells(6, 5).Value = sAr(i-1) End If Next End Sub 

Beware that each word is truly separated by a “.” I copied your text and noticed that “Tab by” is not separated.

 Sub ExtractCharCode() s = Cells(7, 4).Value For i = 1 To Len(s) Cells(i, 8).Value = Mid(s, i, 1) Cells(i, 9).Value = Asc(Mid(s, i, 1)) Next End Sub 

Update option answer from user zone matzone

Instead of passing a range to a function from matzone, I would only pass the value and add a trimmer to it

 Public Function TakeBeforeTab2(s As String) As String s = Mid(s, 1, InStr(UCase(s), "TAB") - 1) TakeBeforeTab2 = Trim(Mid(s, InStr(s, " ") + 1)) End Function 

Answer 3

To get "0.5" from "take 0.5 Tab by mouth 2 times daily."

 Public Function TakeBeforeTab(r As Range) As String Dim s As String s = r.Value s = Mid(s, 1, InStr(UCase(s), "TAB") - 2) TakeBeforeTab = Mid(s, InStr(s, " ") + 1) End Function