?
Solved

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

Posted on 2014-09-17
11
Medium Priority
?
481 Views
Last Modified: 2014-09-18
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!
0
Comment
Question by:Gilbert L.
[X]
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
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 6

Expert Comment

by:johnb25
ID: 40329047
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

Open in new window

0
 

Author Comment

by:Gilbert L.
ID: 40329076
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40329077
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

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40329080
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 Comment

by:Gilbert L.
ID: 40329096
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 Comment

by:Gilbert L.
ID: 40329109
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
 
LVL 6

Assisted Solution

by:johnb25
johnb25 earned 800 total points
ID: 40329154
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

Open in new window

0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 1200 total points
ID: 40329182
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

Open in new window


Regards,
-Glenn
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 40330104
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 Comment

by:Gilbert L.
ID: 40330491
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 40330610
You're welcome.
0

Featured Post

Optimum High-Definition Video Viewing and Control

The ATEN VM0404HA 4x4 4K HDMI Matrix Switch supports 4K resolutions of UHD (3840 x 2160) and DCI (4096 x 2160) with refresh rates of 30 Hz (4:4:4) and 60 Hz (4:2:0). It is ideal for applications where the routing of 4K digital signals is required.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When asking a question in a forum or creating documentation, screenshots are vital tools that can convey a lot more information and save you and your reader a lot of time
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

800 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