VB script to find next number in paragraph

I'm trying to devise a script that will find the next number after a text string then copy it.  Currently I have a script that will do the job if the number occurs a predictable distance from the text string, but recently I've learned that the distance between the two may change randomly!

Here is an example of my text string:
WHITE BLOOD CELL COUNT                              7.2    K/UL               (4.0-    11.0)
RED BLOOD CELL COUNT                               3.19 L  M/UL              (4.00-    5.50)
HEMOGLOBIN                                          9.5 L  G/DL              (12.0-    16.0)
HEMATOCRIT                                         26.1 L  %                 (37.0-    47.0)
MEAN CORPUSCULAR VOLUME                              89    FL                  (82-      98)
MEAN CORPUSCULAR HGB                               29.7    PG                (27.0-    31.0)

Here is an example of my code:
Private Sub Command671_Click()

        'Finds string in LabPaste.
        Dim intWhere As String
        intWhere = InStr(1, [LabPaste], "WHITE BLOOD CELL COUNT")
        'Selects string
        Text670.SelStart = intWhere - -50
        Text670.SelLength = 4
        'Copies string
        DoCmd.RunCommand acCmdCopy
        'Pastes string
        DoCmd.RunCommand acCmdPaste
    Exit Sub

End Sub

Open in new window

I need it to find "WHITE BLOOD CELL COUNT" then copy "7.2"

Any help would be greatly appreciated!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Where are you pasting the results?

This does not copy/paste anything, but it will return the results you need - which you can place in a textbox, or use however needed:

Dim s()  as String
Dim strWBCC as string
s = Split(LabPaste, "WHITE BLOOD CELL COUNT")
strWBCC = Split(Trim(s(1)), " ")(0)
msgbox "White Blood Cell Count = " & strWBCC

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
If you absolutely have to, you could place the results of the above code in a textbox and copy paste it as in your original code... but I'm curious what the end purpose of that value is, because I think you can possibly use the results from the code I posted without that added complexity.
I don't know how your text lines might be delimited, but you might also consider using RegExp to parse the lines

Here is the pattern for each line's text:
^((\w* )*)\s*(\d\S*)\s*(.*)\s*\((\S*)\-\s*(\S*)\)$

Open in new window

A sample routine to parse your lines and return one of the values for one of the lines might look like this:

Public Function ParseLabs(parmString, parmLookFor, parmReturn) As String
    Dim strParsed() As String
    Dim oRegEx As New RegExp
    Dim colMatches As MatchCollection
    Dim objMatch As Match
    Dim objSubMatch As Match
    Dim lngSubmatch As Long
    Dim lngLoop As Long
    With oRegEx
        .Pattern = "^((\w* )*)\s*(\d\S*)\s*(.*)\s*\((\S*)\-\s*(\S*)\)$"
        .IgnoreCase = True
        .Global = True
    End With
    strParsed = Split(parmString, vbCrLf)
    For lngLoop = 0 To UBound(strParsed)
        If strParsed(lngLoop) Like parmLookFor Then
            If oRegEx.Test(strParsed(lngLoop)) Then
                Set colMatches = oRegEx.Execute(strParsed(lngLoop))
                ParseLabs = colMatches(0).SubMatches(parmReturn)
            End If
            Exit Function
        End If
End Function

Open in new window

Testing this in the Immediate window:
L=L & "_____________________________________________" & vbcrlf
L=L & "¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯" & vbcrlf
L=L & "WHITE BLOOD CELL COUNT                              7.2    K/UL               (4.0-    11.0)" & vbcrlf
L=L & "RED BLOOD CELL COUNT                               3.19 L  M/UL              (4.00-    5.50)" & vbcrlf
L=L & "HEMOGLOBIN                                          9.5 L  G/DL              (12.0-    16.0)" & vbcrlf
L=L & "HEMATOCRIT                                         26.1 L  %                 (37.0-    47.0)" & vbcrlf
L=L & "MEAN CORPUSCULAR VOLUME                              89    FL                  (82-      98)" & vbcrlf
L=L & "MEAN CORPUSCULAR HGB                               29.7    PG                (27.0-    31.0)" & vbcrlf


?ParseLabs(L,"* HGB*",2)

?"Low normal: " & ParseLabs(L,"HEMATOCRIT*",4), "High normal: " & ParseLabs(L,"HEMATOCRIT*",5)
Low normal: 37.0            High normal: 47.0

Open in new window

The RegExp submatches for this pattern are:
0 = lab test name
1 = n/a
2 = lab result (numeric)
3 = units
4 = low normal
5 = high normal

Note: You will need to add a reference to the Microsoft VBScript Regular Expressions library to run this code (as posted).
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

DrjdhAuthor Commented:
Works perfectly mbizup!  Can you tell me how it works?

You are a genuis.

Thank you for the extra explanation!
too late :-(
Glad that helped out...

The code is based on the Split() function, which returns an array of elements from splitting a string with a given delimiter:

StringArray = Split(YourString, YourDelimiter)

It uses the split function twice.

The first call separates the text based on the delimiter "WHITE BLOOD CELL COUNT".  This returns an array whose first element contains everything before that, and second element contains everything after that.  Array elements are zero based, so the first element is 0, then 1, 2, etc.
s = Split(LabPaste, "WHITE BLOOD CELL COUNT")

Open in new window

The code then focuses on the second element, s(1), of the array and
1) trims leading/trailing spaces
2) Splits the string into into an array, using a space as the delimiter.  
strWBCC = Split(Trim(s(1)), " ")

Open in new window

Since the code has trimmed all leading spaces, we know that the first element of the array contains the number you are looking for (this is the first text between WHITE BLOOD CELL COUNT, and  the next group of spaces).  The (0) following the split function returns this first element of the array:

strWBCC = Split(Trim(s(1)), " ")(0)

Open in new window

That's the gist of it - but you might also want to look up the Split() function in VBA help.  It is a very useful function :-)
DrjdhAuthor Commented:
Very helpful of you to explain this.  Thank you so much for your help with my programming!
DrjdhAuthor Commented:
I just have one other question regarding the script.  What is the best way to error trap when the text string is not found?

Here is my final code, I had to add for the instance that there is a "<" or ">" before the number.

s = Split(LabPaste, "WHITE BLOOD CELL COUNT")
strLab = Split(Trim(s(1)), " ")(0)
strLab2 = Split(Trim(s(1)), " ")(1)
If (strLab = "<" Or strLab = ">") Then
Text707 = strLab
Text601 = strLab2
Text707 = ""
Text601 = strLab
End If
End Sub

Open in new window

Give this a try:

Dim s()  as String
Dim strWBCC as string
s = Split(LabPaste, "WHITE BLOOD CELL COUNT")
If Ubound(s) > 0 then 
    strWBCC = Split(Trim(s(1)), " ")(0)
    msgbox "White Blood Cell Count = " & strWBCC
    msgbox "The text was not found"
end if

Open in new window

It checks the array dimensions after splitting based on White Blood Cell Count and skips the rest of code if that text is not found (the upper bound of the array is 0 in this case).
DrjdhAuthor Commented:
Thanks mbizup!
You're very welcome  :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.