Searching a string for numbers including decimals

问题:

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?

回答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)

回答2:

Update using Tab as relevant indicator

Assuming that Tab is the relevant indicator you could do the follwing:

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 really seperated by a " ". I copied your text and noticed that "Tab by" was not seperated.

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 using a variation of the answer from user matzone

Instead of passing a range into the function from matzone i would only pass the Value and add a trim 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

回答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