top of page

Excel Macro to extract sentences containing a particular word


I found the below macro at: http://www.experts-exchange.com/Software/Office_Productivity/Q_28520535.html

It can be used to find sentences in an Excel column which contain a particular word. Change the Range from

Range("A1", Range("A1").End(xlDown)) to show the cells at the top and bottom of the column with the text you are searching. This macro is set to look for the strings EMR and EHR. The search is case sensitive.

See the demonstrative video here:

Sub ListPhrases() Dim rng As Range Dim cl As Object Dim arrPhrases() As String Dim x, c As Integer 'Change "A1" to the first cell where sentences occur to test Set rng = Range("A1", Range("A1").End(xlDown)) For Each cl In rng arrPhrases = Split(cl.Value, ",") c = 1 For x = 0 To UBound(arrPhrases) If InStr(arrPhrases(x), "EHR") > 0 Then cl.Offset(0, c).Value = Trim(arrPhrases(x)) c = c + 1 ElseIf InStr(arrPhrases(x), "EMR") > 0 Then cl.Offset(0, c).Value = Trim(arrPhrases(x)) c = c + 1 End If Next x Next cl End Sub


 
 

Recent Posts

See All
How does Relativity use AI data?

How is data generated by aiR utilized by Relativity? Relativity has published a white paper addressing its AI security policies assuring...

 
 

Sean O'Shea has more than 20 years of experience in the litigation support field with major law firms in New York and San Francisco.   He is an ACEDS Certified eDiscovery Specialist and a Relativity Certified Administrator.

​

The views expressed in this blog are those of the owner and do not reflect the views or opinions of the owner’s employer.

​

If you have a question or comment about this blog, please make a submission using the form to the right. 

Your details were sent successfully!

© 2015 by Sean O'Shea . Proudly created with Wix.com

bottom of page