# Excel formula/macro - How can I identify a 3 character word & then pull the entire sentence to an adjacent cell?

Hi.  I have a cell with a lot of technology names.

I need to only pull sentences with EHR or EMR within the sentence, and then pull the entire sentence (between the commas) into an adjacent cell.

For example:

,Allscripts Enterprise EHR, Cerner, Cerner Millennium, Certify HealthDock,

I want to pull only "Allscripts Enterprise EHR" in another cell.

Thanks!
###### Who is Participating?
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.

Commented:
Hi,

If your starting sentence is in A1 in Sheet1, then this will do it. It will put each sub-section that has the letters you are searching in the next cell to the right

John

``````Function FindPhrase()

Dim WB As Workbook
Dim WS As Worksheet
Dim InputValue As String
Dim Sentence() As String
Dim A As Integer
Dim B As Integer

Set WB = ThisWorkbook
Set WS = WB.Worksheets("Sheet1")
B = 2

InputValue = WS.Cells(1, 1).Value

Sentence = Split(InputValue, ",")

For A = LBound(Sentence) To UBound(Sentence)

If InStr(Sentence(A), "EHR") > 0 Then
WS.Cells(1, B).Value = Sentence(A)
B = B + 1
ElseIf InStr(Sentence(A), "EMR") > 0 Then
WS.Cells(1, B).Value = Sentence(A)
B = B + 1
End If

Next

End Function
``````
0
Author Commented:
Sorry for my ignorance, but I tried to create a macro from this and it does not work with the Sub.  How can I engage the code?

Thanks.
0
Excel VBA DeveloperCommented:
John's code isn't complete, but the right idea.

If you want a user-defined function to return the first substring with either EHR or EMR in it, then use this code:
``````Function FindPhrase(InputValue As String) As String
Dim Sentence() As String
Dim A As Integer

Sentence = Split(InputValue, ",")
For A = LBound(Sentence) To UBound(Sentence)
If InStr(Sentence(A), "EHR") > 0 Then
FindPhrase = Trim(Sentence(A))
ElseIf InStr(Sentence(A), "EMR") > 0 Then
FindPhrase = Trim(Sentence(A))
End If
Next A
End Function
``````
0
Excel VBA DeveloperCommented:
What do you want to have happen if any of the substrings occur more than once in a cell?  For example:

here is some text with EHR, then another string, then another with EMR.

By the way, I forgot to mention in the previous post, you use the UDF in Excel like a regular function.  Just type in an adjacent cell
=FindPhrase(cell_reference)
and it should return the last-occurring substring.

Regards,
-Glenn
0
Author Commented:
Also, I have 456 rows to look at.  When I tried it with Alt F11, Run - It only did A1.  Can it go all the way down?
0
Author Commented:
Glen, your code works but some of the records may have both EMR and EHR and the code seems to only pick one or the other
0
Commented:
I have amended the code to allow you select the range of cells that have long sentences.
I will loop through and do what the first code did for each one.

Glenn,
The B=B+1 in the original code was used to put each segment out in the next column.

John

``````Function FindPhrase()

Dim WB As Workbook
Dim WS As Worksheet
Dim InputRange As Range
Dim InputValue As String
Dim Sentence() As String
Dim A As Integer
Dim B As Integer

Set WB = ThisWorkbook
Set WS = WB.Worksheets("Sheet1")

Set InputRange = Application.InputBox(prompt:="Select range of sentences", Type:=8)

For Each c In InputRange
InputValue = c.Value
Sentence = Split(InputValue, ",")
For A = LBound(Sentence) To UBound(Sentence)
B = 1
If InStr(Sentence(A), "EHR") > 0 Then
c.Offset(0, B).Value = Sentence(A)
B = B + 1
ElseIf InStr(Sentence(A), "EMR") > 0 Then
c.Offset(0, B).Value = Sentence(A)
B = B + 1
End If

Next
Next
End Function
``````
0
Excel VBA DeveloperCommented:
Unfortunately, john's code is set up as a function and only runs on one cell.

If all your data (sentences) are in column A, starting in cell A1, this code will loop through all of them and insert the substrings in adjacent column(s), starting in column B:
``````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
``````

Regards,
-Glenn
0

Experts Exchange Solution brought to you by

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

IT Services ConsultantCommented:
Gilbert:

The contributions so far are checking for any existence of the three-character literal strings; not explicitly just those three characters distinct from any other text.

This may well address your requirements & allow you to retrieve the correct results as intended.

However, for example:

",Allscripts Enterprise EHR, Cerner, Cerner Millennium, Certify HealthDock,"

"EHR" would be detected & "Allscripts Enterprise EHR" would be removed (as intended).

BUT... what if the search text looked like this:

"FUEHRER,Allscripts Enterprise EHR, Cerner, Cerner Millennium, Certify HealthDock,"

"FUEHRER" would then be identified as containing "EHR" & this is perhaps an incorrect result.

Please could you clarify if the search terms ("EHR" & "EMR") need to be distinct (discrete) values or whether the inclusion of the three characters (in order) within a larger collection of characters would also be considered a 'match', or not?

Thank you.
0
Author Commented:
Thanks John/Glenn!  This has been immensely helpful!  What I finally did was take the macro and created one for EMR and one for EHR, ran them separately and then concatenated them into a column for EMR and a column for EHR.
0
IT Services ConsultantCommented:
You're welcome.
0
###### 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
Office Productivity

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.