Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-16-2019, 09:53 AM
cancerresearcher cancerresearcher is offline Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Mac OS X Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Office 2016
Novice
Macro in Word to find specific words? Will be used for good cause (prostate cancer research)
 
Join Date: Jul 2019
Posts: 6
cancerresearcher is on a distinguished road
Default Macro in Word to find specific words? Will be used for good cause (prostate cancer research)

Ok so I'm working on doing some data mining from an electronic medical record, but using Microsoft Word (or any other program that you guys may know of) to help me find keywords (from text that I paste into the document) without having to search for them manually.
How this would work ideally would be something like this:
1. have a chart with multiple specific keywords in Column A.
2. Paste text from electronic medical record into Microsoft Word (or any other program)
3. the macro searches for the keywords I assign to it
4. If keywords are found, it in the text field, it would paste the word immediately before or after the keyword.

For example, I may have multiple keywords in this document, but one keyword that Word would look for would be a SHIM score. Hence, word would search the document for "SHIM" from within the text that I paste into the macro. I then paste a large amount of text into Word and it searches for SHIM, BUT the macro pastes the values found immediately before or after the keyword in the document. Let's say in the document it says "SHIM 25" so in the chart, under SHIM, it will display the number 25 for me automatically in the second column.

Something like this would be immensely helpful in data mining. It would allow my team and me to enter data far quicker and produce lots of peer reviewed research in a much quicker fashion. This research would go on to help men all over the world with prostate cancer.

Any assistance to help with this would be GREATLY appreciated. If it's not possible, I am also happy to accept this outcome but figured I would reach out to some experts in this forum first for help.



Thanks so much in advance!
Reply With Quote
  #2  
Old 07-16-2019, 11:16 AM
cancerresearcher cancerresearcher is offline Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Mac OS X Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Office 2016
Novice
Macro in Word to find specific words? Will be used for good cause (prostate cancer research)
 
Join Date: Jul 2019
Posts: 6
cancerresearcher is on a distinguished road
Default

also if it's not possible to find the word after the search term, finding the search term alone would be helpful too.
Reply With Quote
  #3  
Old 07-16-2019, 09:44 PM
gmayor's Avatar
gmayor gmayor is offline Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Windows 10 Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

As someone who has, so far successfully, been treated for prostate cancer over the past few years, this is one cause I am happy to assist with, however without access to examples of the source documents and a list of what you want to search for and expect to find in relation to those texts, it is difficult to provide that assistance. However the following will find words or phrases from a list followed by a space and a number, which might help get you started.

I don't have the Mac version of Word, but it should still work.
Code:
Sub Macro1()
Dim vFindText As Variant
Dim oRng As Range
Dim oColl As Collection
Dim oSource As Document, oTarget As Document
Dim i As Long, j As Integer
    Set oSource = ActiveDocument
    Set oTarget = Documents.Add
    Set oColl = New Collection
    vFindText = Array("SHIM", "WORD2", "WORD3")    'list the words or phrases to find
    For i = 0 To UBound(vFindText)
        Set oRng = oSource.Range
        With oRng.Find
            .ClearFormatting
            .Replacement.ClearFormatting
            Do While .Execute(FindText:=vFindText(i) & "[ .0-9]{1,}", _
                              MatchWildcards:=True)
                oTarget.Range.InsertAfter oRng.Text & vbCr
                oRng.Collapse wdCollapseEnd
            Loop
        End With
    Next i
lbl_Exit:
    Exit Sub
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #4  
Old 07-17-2019, 10:02 AM
cancerresearcher cancerresearcher is offline Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Mac OS X Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Office 2016
Novice
Macro in Word to find specific words? Will be used for good cause (prostate cancer research)
 
Join Date: Jul 2019
Posts: 6
cancerresearcher is on a distinguished road
Default

THANK YOU so much, and I certainly hope your treatment(s) have gone as planned. I work alongside some of the top key opinion leaders in the USA so feel free to send me a PM with any questions about your care (should you have any).

OK so I'm really a total novice when it comes to this stuff. Basically, I inserted your code into the VBA editor in Microsoft Word, then tried to run it based on some text I pasted into a document. What happens when I run the macro is that it opens a new, untitled blank document, but does nothing else.

I did attach a document that shows an example of what I'm trying to achieve, which would probably make this easier to understand. If you can provide any further assistance, I would appreciate it tremendously!
Attached Files
File Type: docx keyword search example.docx (13.2 KB, 10 views)
Reply With Quote
  #5  
Old 07-17-2019, 03:28 PM
gmaxey gmaxey is offline Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Windows 10 Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,422
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

Graham is on the other side of the world and it will be awhile before he is back around. Based on your example scenario, I am assuming that the terms e.g., PSA will appear once in the text to process.

The following might work were you define the number of following words to include in your term definition table. See attached:

Code:
Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oTbl As Table
Dim arrTerm() As String
Dim lngIndex As Long
Dim oRng As Range

  Set oTbl = ActiveDocument.Tables(1)
  ReDim arrTerm(oTbl.Rows.Count - 2, 1)
  For lngIndex = 2 To oTbl.Rows.Count
    arrTerm(lngIndex - 2, 0) = fcnGetCellText(oTbl.Cell(lngIndex, 1))
    arrTerm(lngIndex - 2, 1) = fcnGetCellText(oTbl.Cell(lngIndex, 2))
  Next lngIndex
  For lngIndex = 0 To UBound(arrTerm, 1)
    Set oRng = ActiveDocument.Range
    oRng.Start = oTbl.Range.End
    With oRng.Find
      .Text = arrTerm(lngIndex, 0)
      If .Execute Then
         Select Case True
           Case IsNumeric(arrTerm(lngIndex, 1))
              oRng.Collapse wdCollapseEnd
              oRng.MoveEnd wdWord, Val(arrTerm(lngIndex, 1)) + 1
              oTbl.Cell(lngIndex + 2, 3).Range.Text = Trim(oRng.Text)
           Case arrTerm(lngIndex, 1) = "T/F"
             oTbl.Cell(lngIndex + 2, 3).Range.Text = "True"
         End Select
      Else
        Select Case True
           Case IsNumeric(arrTerm(lngIndex, 1))
             oTbl.Cell(lngIndex + 2, 3).Range.Text = vbNullString
           Case arrTerm(lngIndex, 1) = "T/F"
             oTbl.Cell(lngIndex + 2, 3).Range.Text = "False"
         End Select
      End If
    End With
  Next lngIndex
lbl_Exit:
  Exit Sub
  
End Sub
Function fcnGetCellText(oCell As Cell) As String
  fcnGetCellText = Left(oCell.Range.Text, Len(oCell.Range.Text) - 2)
lbl_Exit:
  Exit Function
End Function
Attached Files
File Type: docx keyword search example.docx (13.8 KB, 9 views)
File Type: docm keyword search example.docm (19.4 KB, 9 views)
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #6  
Old 07-17-2019, 04:07 PM
Guessed's Avatar
Guessed Guessed is offline Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Windows 10 Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

I recommend you find a standard way to set up the source info before running the macro. Based on what you have said and the sample doc you posted, I would setup the document to start with a two column table, the first column would be filled with the key search terms and the second column would be empty waiting for the macro to run. Then following the table you could paste in the content from your EMR.

The macro that suits this layout and workflow might then look like this.
Code:
Sub Extractor()
  Dim oRng As Range, oTable As Table, oCell As Cell, oNextCell As Cell
  Dim sFind As String, oRngCell As Range
  
  Set oTable = ActiveDocument.Tables(1)
  Set oRng = ActiveDocument.Range(oTable.Range.End, ActiveDocument.Range.End)
  For Each oCell In oTable.Columns(1).Cells
    sFind = Split(oCell.Range.Text, vbCr)(0)
    Set oRngCell = oCell.Next.Range
    With oRng.Find
      .ClearFormatting
      .Replacement.ClearFormatting
      .MatchWildcards = False
      Do While .Execute(FindText:=sFind)
        oRng.MoveStart Unit:=wdWord, Count:=-2
        oRng.MoveEnd Unit:=wdWord, Count:=4
        oRngCell.InsertBefore oRng & vbCr
        oRng.Collapse direction:=wdCollapseEnd
      Loop
      Set oRng = ActiveDocument.Range(oTable.Range.End, ActiveDocument.Range.End)
    End With
  Next oCell
End Sub
Attached Files
File Type: docm keyword search example.docm (20.5 KB, 13 views)
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #7  
Old 07-17-2019, 09:13 PM
gmayor's Avatar
gmayor gmayor is offline Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Windows 10 Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

It seems that fellow contributors have been busy while I slept. The original macro didn't work because the search criteria are not as you said. The problem with manuscripts of free text is that they don't always conform to standards so they might not always give the expected results, but based upon your sample I would suggest the following, again bearing in mind that I have not tested it on the Mac version of Word.

It works on the premise that you paste the text into a document and run the macro. The macro adds a table at the top of the page and if the terms are found, it adds them to the first column and highlights them in the text. It then attempts to find the required values associated with them and puts them in the adjacent columns.

As for my own treatment I was diagnosed and treated in an award winning oncology centre (one of the best in Europe) five years ago. All currently appears well though it took a long time to shake off the after effects of the hormone treatment that followed. I finished treatment two years ago. I see my oncologist every six months and I am still alive and well

Code:
Sub Macro1()
Dim vFindText As Variant
Dim oRng As Range
Dim oTable As Table
Dim oCell As Range
Dim oDoc As Document
Dim i As Long, j As Integer
    vFindText = Array("AUA", "SHIM", "PSA", "TURBT", "Frequency")
    Set oDoc = ActiveDocument
    oDoc.Range.InsertParagraphBefore
    Set oRng = oDoc.Range
    oRng.Collapse 1
    Set oTable = oDoc.Tables.Add(oRng, 5, 2)
    For i = 0 To 4
        Set oRng = oDoc.Range
        With oRng.Find
            .ClearFormatting
            .Replacement.ClearFormatting
            Do While .Execute(FindText:=vFindText(i))
                Select Case i
                    Case 0, 1, 2
                        oRng.MoveEndUntil "0123456789"
                        oRng.Collapse 0
                        oRng.MoveEndWhile "0123456789/."
                        Set oCell = oTable.Cell(i + 1, 1).Range
                        oCell.End = oCell.End - 1
                        oCell.Text = vFindText(i)
                        Set oCell = oTable.Cell(i + 1, 2).Range
                        oCell.End = oCell.End - 1
                        oCell.Text = oRng.Text
                    Case Else
                        Set oCell = oTable.Cell(i + 1, 1).Range
                        oCell.End = oCell.End - 1
                        oCell.Text = vFindText(i)
                        Set oCell = oTable.Cell(i + 1, 2).Range
                        oCell.End = oCell.End - 1
                        oCell.Text = True
                End Select
            Loop
        End With
    Next i
lbl_Exit:
    Set oDoc = Nothing
    Set oRng = Nothing
    Set oTable = Nothing
    Set oCell = Nothing
    Exit Sub
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #8  
Old 07-18-2019, 06:15 AM
gmaxey gmaxey is offline Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Windows 10 Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,422
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

Graham,


Looks good. I would only change:
oRng.MoveEndWhile "0123456789/" 'eliminated the period.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #9  
Old 07-18-2019, 07:44 PM
gmayor's Avatar
gmayor gmayor is offline Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Windows 10 Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

Greg
What if the number has a decimal point?
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #10  
Old 07-18-2019, 08:10 PM
gmaxey gmaxey is offline Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Windows 10 Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,422
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

Graham,


True. Didn't think of that as a decimal wasn't in the example text. So, yes leave your line as it and append to your existing:

oCell.End = oCell.End - 1
oCell.Text = oRng.Text
If Right(oCell.Text, 1) = "." Then oCell.Text = Left(oCell.Text, _
Len(oCell.Text) - 1)


To eliminate the period (when it is a period).
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #11  
Old 07-19-2019, 10:26 AM
cancerresearcher cancerresearcher is offline Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Mac OS X Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Office 2016
Novice
Macro in Word to find specific words? Will be used for good cause (prostate cancer research)
 
Join Date: Jul 2019
Posts: 6
cancerresearcher is on a distinguished road
Default Thank you!!!

Thank you everyone for your help! I will play with what you sent and get back with you all ASAP
Reply With Quote
  #12  
Old 07-22-2019, 08:54 AM
cancerresearcher cancerresearcher is offline Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Mac OS X Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Office 2016
Novice
Macro in Word to find specific words? Will be used for good cause (prostate cancer research)
 
Join Date: Jul 2019
Posts: 6
cancerresearcher is on a distinguished road
Default

Hello everyone!
Thanks so much for your help! Can anyone tell me how I can input the latest code from gmayor into the document? I also got gmaxey's attachment and it seems like everything works GREAT! One question I had is how I would add additional rows and keywords to the table so I can change/modify the keywords so the word document searches for them automatically (for other studies, for instance that may have different keywords). If we had the ability to do this, it literally would be a GAME CHANGER. I could put out 2-3x the amount of research with a tool like this. I genuinely appreciate everyone's help in this matter! Even if it was a table with like 50 blank rows or something, but whenever I put a keyword into column A, it would do the search automatically in column B.

Is something like this possible? Thank you thank you so much for your help!!!!
Reply With Quote
  #13  
Old 07-22-2019, 10:41 AM
gmaxey gmaxey is offline Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Windows 10 Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,422
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

Take a look at the attached. It combines what I felt were the best ideas of both Graham and I where you start with a table to define the search terms.
Attached Files
File Type: docm keyword search example-1.docm (24.3 KB, 13 views)
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #14  
Old 07-22-2019, 09:30 PM
gmayor's Avatar
gmayor gmayor is offline Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Windows 10 Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

The problem with adding words - in the case of Greg's version to a table. In the case of mine to an array, is not the addition of terms to either, but the processing required to get the required text associated with those terms. Finding the terms is simple enough but it is the results associated with the terms that are at issue.

Greg has made some sensible choices to enable you to input terms and report TRUE, and also to select particular values a set distance from the found term e.g. XXX and I would have used Mr and not Mr X to find the age of the person. His version doesn't highlight the terms, but that's an easy addition.

Unfortunately with manuscripts that do not conform to a standard pattern pasted into the document it can still be a bit hit and miss when trying to extract the results, and one sample is insufficient to produce more accurate results - and given the nature of what you are doing, I would have wanted the results to be accurate.

Had I been doing this I would have started with a form with a fixed layout using content controls or fields to enter the required pieces of information, but that would mean changing the 'electronic medical record' that you paste from.

What format is that medical record in? Would it be possible to read directly from that record as the chances are that it will be in a more structured format?

If you want to supply some more information and some further samples including a full list of terms to find, you can send them to the contact link on my web site (add your forum username to the subject so it won't get swept away by my spam filters) and I will see what else needs to be done.

I can share them privately with Greg (who is a friend of long standing) if you wish.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #15  
Old 07-23-2019, 01:41 PM
cancerresearcher cancerresearcher is offline Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Mac OS X Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Office 2016
Novice
Macro in Word to find specific words? Will be used for good cause (prostate cancer research)
 
Join Date: Jul 2019
Posts: 6
cancerresearcher is on a distinguished road
Default

GMAXEY
Thank you SO MUCH for your help with this! GMAYOR I will certainly contact you with more details. The medical record is stored on a system called EPIC, which is an online electronic medical record system. I merely copy the data from EPIC and paste it into some sort of office program so that I can do searches on the text. I usually paste medical record information that can be anywhere from 6-12 pages long. Doing the search makes it far easier than reading each medical record one by one to find the data I need.

Gmaxey, is there a way to perhaps include a text box that I can paste data in to or something so the macro knows what area to search for? I'm not sure how your macro searches for the data, like if it's limited to a certain space in the actual document?

Thank you guys again so much. This is so very promising and it makes me VERY excited about how much time it could save/more data we can publish!
Reply With Quote
Reply

Tags
keyword, macro, search

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to find (highlight) two and more words in a list of 75k single words in Word 2010 Usora Word 8 05-29-2018 03:34 AM
Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Macro to search for specific words in a document mike0215 Word VBA 2 11-28-2017 07:25 AM
VBA Word - Find & Apply Styles to Specific Words - Using Case Statement jc491 Word VBA 17 12-26-2015 12:25 PM
Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Need VBA For Macro On How To Remove Specific Words netchie Word VBA 6 08-28-2012 03:37 PM
Macro in Word to find specific words? Will be used for good cause (prostate cancer research) Macro for highlighting specific number of words icsjohn Word VBA 2 12-07-2011 06:44 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:49 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft