[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


VB script to find next number in paragraph

Posted on 2012-04-07
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
LVL 61

Accepted Solution

mbizup earned 2000 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 46

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).
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


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 46

Expert Comment

ID: 37826086
too late :-(
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

656 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