VB script to find next number in paragraph

Posted on 2012-04-07
Last Modified: 2012-06-21
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!
Question by:Drjdh
  • 5
  • 4
  • 2
LVL 61

Accepted Solution

mbizup earned 500 total points
ID: 37825817
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

LVL 61

Expert Comment

ID: 37825823
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.
LVL 45

Expert Comment

ID: 37826069
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).

Author Closing Comment

ID: 37826077
Works perfectly mbizup!  Can you tell me how it works?

You are a genuis.

Thank you for the extra explanation!
LVL 45

Expert Comment

ID: 37826086
too late :-(
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

LVL 61

Expert Comment

ID: 37826110
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 :-)

Author Comment

ID: 37826143
Very helpful of you to explain this.  Thank you so much for your help with my programming!

Author Comment

ID: 37866214
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

LVL 61

Expert Comment

ID: 37866420
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).

Author Comment

ID: 37874365
Thanks mbizup!
LVL 61

Expert Comment

ID: 37874384
You're very welcome  :-)

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now