Begin a new line in cell when key words are entered

[skyport]

What I'm hoping to have is a code that will say have a list of precise key words in column A on a page to be named "words" so that new ones could be added from time to time.

On page 2 of the work book, in column G, text would be entered into cells by either typing or pasting existing text from another source. Each time a key word from the word page is encountered in a cell in column G on page 2 from the text pasted or typed, it would take that key word and the text that follows and make it begin on the next line WITHIN that cell.

EXAMPLE:

Say the words list contained the key words Impression: and Diagnosis:


The data entered in a single cell in column G may look like this when typed or pasted:

The person involved had the doctor review the accident. Impression: The complaint was shoulder pain. Diagnosis: Shoulder sprain.

That same data when the code is ran would look like the following in the single cell:

The person involved had the doctor review the accident.
Impression: The complaint was shoulder pain.
Diagnosis: Shoulder sprain.


Any help would be much appreciated.

 

[FormR]

You could try something like this, which would go into the "Page 2" sheets code module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range, w As Range
Application.EnableEvents = False
Set r = Intersect(Target, Columns("G"))
If Not r Is Nothing Then
    For Each c In r
        With Sheets("Words")
            For Each w In .Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
                If InStr(1, c.Value, w.Value, vbTextCompare) > 0 Then
                    c.Replace w.Value, vbLf & w.Value, LookAt:=xlPart
                End If
            Next w
        End With
        c.EntireRow.AutoFit
    Next c
End If
Application.EnableEvents = True
End Sub

 

[skyport]

Thanks for helping. I put it in a new module but when I do the alt-F8, it doesn't show up for me to run it.

 

[Rick Rothstein]

Thanks for helping. I put it in a new module but when I do the alt-F8, it doesn't show up for me to run it.

FormR posted what is known as "event code"... it has to go in the worksheet module, not a general module where macros go.

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[FormR]

EDIT: Thanks for stepping in Rick.

 

[Rick Rothstein]

By the way, if you want a macro (instead of the event code), give this one a try...

Sub ReplaceWords()
  Dim R As Long, Words As Variant
  Words = Sheets("Words").Range("A1", Cells(Rows.Count, "A").End(xlUp))
  For R = 1 To UBound(Words)
    Sheets("Sheet2").Columns("G").Replace Words(R, 1), vbLf & Words(R, 1), xlPart
  Next
End Sub

 

[skyport]

Rick, Always great to see you again. Thanks to you both for helping out. I got it done per your suggestion and the following is the result:

source text: ( consider the key words are in bold for illustration only ) They are each on the word list and please note some are multiple words and with a colon as well. For example: Subjective Complaints: is all in one cell in the word list because that phrase is considered a key word

Here is how text looks before the code:

Subjective Complaints: Persistent pain in neck bilaterally with headaches radiating numbness and tingling symptoms in upper extremity. Low back pain is 50% improved since radiofrequency ablation treatment.Current medications: Vicoprofen, Relafen and Prilosec.Diagnoses: Neck pain-minor discogenic and changes in small spur complexes. Low back pain, disc disease at L5-S1, mild bilateral foraminal narrowing. No stenosis. Abnormal electrodiagnostic studies, nerve conduction study was positive for left mild carpal tunnel syndrome, but otherwise normal. Plan: It is not believe that carpal tunnel syndrome is what is bothering her the most. She will continue her current medications. Work status: Remains unchanged.

And after the code: ( again the key words or word phrases in bold )

Subjective
Complaints: Persistent pain in neck bilaterally with headaches radiating numbness and tingling symptoms in upper extremity. Low back pain is 50% improved since radiofrequency ablation treatment. Current
Medications: Vicoprofen, Relafen and Prilosec.
Diagnoses: Neck pain-minor discogenic and changes in small spur complexes. Low back pain, disc disease at L5-S1, mild bilateral foraminal narrowing. No stenosis. Abnormal electrodiagnostic studies, nerve conduction study was positive for left mild carpal tunnel syndrome, but otherwise normal.
Plan: It is not believe that carpal tunnel syndrome is what is bothering her the most. She will continue her current
Medications.


Work status: Remains unchanged.

Conclusion: Ut seems to successfully treat the single key words correctly however it splits the word phrases onto two lines and in the case of the last one, note that it kept the entire word phrase ( work status: ) on the same line but it dropped it down two lines instead of one.

Below is how I need it turn out:

Subjective Complaints: Persistent pain in neck bilaterally with headaches radiating numbness and tingling symptoms in upper extremity. Low back pain is 50% improved since radiofrequency ablation treatment.
Current Medications: Vicoprofen, Relafen and Prilosec.
Diagnoses: Neck pain-minor discogenic and changes in small spur complexes. Low back pain, disc disease at L5-S1, mild bilateral foraminal narrowing. No stenosis. Abnormal electrodiagnostic studies, nerve conduction study was positive for left mild carpal tunnel syndrome, but otherwise normal.
Plan: It is not believe that carpal tunnel syndrome is what is bothering her the most. She will continue her current
Medications.
Work status: Remains unchanged.

 

[FormR]

Are you using the "event code" or the "macro"?

 

[skyport]

formR

I tried in both the upper window and the immediate, which gives a compile error

 

[skyport]

above was the event code. trying macro now