formula to extract strings containing the keyword "upset" between DATE and ENDNOTE

Jaffey · Sep 30th 2017

Greetings,

I have a table with a "Notes" column where multiple notes from users appear inside a single cell. Each notes starts with DATE and ends with ENDNOTE.

Example of a note within a cell:

DATE 2017-08-23 15:00:55 UTC NOTE:
forgot to go over the Consent letter with the customer since he has no email address.
Please read out the consent to the client or ask for an email address that we can forward it to.
WARNING:
Customer is little bit upset with his situation and may be rude. ENDNOTE\\

My goal is a formula, or series of formulas, that looks for users who have had unsatisfactory experiences by scanning for keywords, such as "upset" or "frustrated".
I have the following formula that returns the first instance of "upset" plus the rest of the string until ENDNOTE plus 20 chars preceding it.

Code

=MID([@Notes],SEARCH("upset",[@Notes])-20,(SEARCH("ENDNOTE",[@Notes],SEARCH("upset",[@Notes]))-SEARCH("upset",[@Notes])+20))

My goal is a formula that extracts the entire string between DATE and ENDNOTE if "upset" and/or "frustrated" etc. appears between them.

I actually have about a dozen keywords and phrases to search for so I would be very open to a solution that used a =index(match( sort of approach using a helper table containing the keywords.

Thanks in advance!
Jeff

**PCI** · Sep 30th 2017

Re: formula to extract strings containing the keyword "upset" between DATE and ENDNOT

Please, can you prepare and attach a short Excel sample.
As for containment a UDF can be used ??

**PCI** · Sep 30th 2017

Re: formula to extract strings containing the keyword "upset" between DATE and ENDNOT

See how next file could help

Jaffey · Oct 4th 2017

Thank you for your response PCI! My goal is to extract the entire string between DATE and ENDNOTE. Alternately, "UTC NOTE:" could be used as the first goal post as it is less likely to generate false positives as "DATE" could easily be entered in a note by a user whereas UTC NOTE is less likely to be entered.

So from my original example above, the desired output should look like this:

2017-08-23 15:00:55 UTC NOTE:
forgot to go over the Consent letter with the customer since he has no email address.
Please read out the consent to the client or ask for an email address that we can forward it to.
WARNING:
Customer is little bit upset with his situation and may be rude.

I have attached a sample spreadsheet with some real notes as well as columns for each keyword I am extracting. In the final column, I have combined them all using one long formula as shown below to produce a summary report of sorts. The customer service manager can then review the extracted comments. Eventually, I would like to replace this formula with an index/match approach using tables where additional keywords can be added easily but first I need to figure out how to extract the full string properly from between the delimiters I am using.

Code

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(MID([@Notes],SEARCH("upset",[@Notes])-20,(SEARCH("ENDNOTE",[@Notes],SEARCH("upset",[@Notes]))-SEARCH("upset",[@Notes])+20)),MID([@Notes],SEARCH("frustrated",[@Notes])-20,(SEARCH("ENDNOTE",[@Notes],SEARCH("frustrated",[@Notes]))-SEARCH("frustrated",[@Notes])+20))),MID([@Notes],SEARCH("complain",[@Notes])-20,(SEARCH("ENDNOTE",[@Notes],SEARCH("complain",[@Notes]))-SEARCH("complain",[@Notes])+20))),MID([@Notes],SEARCH("escalate",[@Notes])-20,(SEARCH("ENDNOTE",[@Notes],SEARCH("escalate",[@Notes]))-SEARCH("escalate",[@Notes])+20))),MID([@Notes],SEARCH("misunder",[@Notes])-20,(SEARCH("ENDNOTE",[@Notes],SEARCH("misunder",[@Notes]))-SEARCH("misunder",[@Notes])+20))),MID([@Notes],SEARCH("not happy",[@Notes])-20,(SEARCH("ENDNOTE",[@Notes],SEARCH("not happy",[@Notes]))-SEARCH("not happy",[@Notes])+20))),MID([@Notes],SEARCH("wrong",[@Notes])-20,(SEARCH("ENDNOTE",[@Notes],SEARCH("wrong",[@Notes]))-SEARCH("wrong",[@Notes])+20))),MID([@Notes],SEARCH("confused",[@Notes])-20,(SEARCH("ENDNOTE",[@Notes],SEARCH("confused",[@Notes]))-SEARCH("confused",[@Notes])+20))),"")

**PCI** · Oct 7th 2017

Just to start see the macro in the file attached: It prepares all strings which fit into your specifications (from UTC NOTE to ENDNOTE) and record these string after the last column.
After we could see to display only strings where exist a particular word
Of course it is a macro but it is to be sure the understanding is good and a result possible

Code

Option Explicit


Sub SplitData()
Const StartStg As String = "UTC NOTE"
Const EndStg As String = "ENDNOTE"
Dim WkStg  As String
Dim LC  As Integer, LR  As Integer, I As Integer, J As Integer
Dim StNb As Integer, EndNb As Integer
Dim SFlg  As Boolean
Dim T
    LR = Cells(Rows.Count, 1).End(3).Row
    For I = 3 To LR
        SFlg = True
        WkStg = Cells(I, 1)
        J = 1
        While SFlg
            With Application
                T = Not (.IsError(.Search(StartStg & "*" & EndStg, WkStg, J)))
                If (T) Then
                    LC = Cells(I, Columns.Count).End(xlToLeft).Column
                    StNb = .Search(StartStg, WkStg, J + 1) + Len(StartStg) + 1
                    EndNb = .Search(EndStg, WkStg, J + 1)
                    Cells(I, LC + 1) = Mid(WkStg, StNb, EndNb - StNb)
                    J = EndNb
                Else
                    SFlg = False
                End If
            End With
        Wend
    Next I
End Sub

Display More

Jaffey · Oct 10th 2017

Thank you PCI for that very useful macro! I notice, however, when I run it against the sample records in my attached file it adds approximately 800 new columns to the table. This seems to be because it is adding a new column for each parsed note in each record.

For example, the first cell in the first row contains 27 notes bounded by "UTC NOTE" and "ENDNOTE". So the macro parses those 27 notes into 27 new columns. Then, when it processes the 43 notes in the first cell of the 2nd row, instead of using the blank cells in the newly created 27 columns, it parses them into 43 new columns starting from column 28.

Would it be possible to start from the first column each time it begins parsing a new record? I apologize if I am not describing this properly but I don't know how else to describe it.

**PCI** · Oct 10th 2017

Clear, try next code

Code

Option Explicit


Sub SplitData()
Const StartStg As String = "UTC NOTE"
Const EndStg As String = "ENDNOTE"
Const FR As Integer = 2
Dim WkStg  As String
Dim LC  As Integer, LR  As Integer, I As Integer, J As Integer
Dim StNb As Integer, EndNb As Integer
Dim SFlg  As Boolean
Dim T
    Application.ScreenUpdating = False
    LR = Cells(Rows.Count, 1).End(3).Row
    LC = Cells(FR, Columns.Count).End(xlToLeft).Column
    For I = FR + 1 To LR
        SFlg = True
        WkStg = Cells(I, 1)
        J = 1
        While SFlg
            With Application
                T = Not (.IsError(.Search(StartStg & "*" & EndStg, WkStg, J)))
                If (T) Then
                    StNb = .Search(StartStg, WkStg, J + 1) + Len(StartStg) + 1
                    EndNb = .Search(EndStg, WkStg, J + 1)
                    Cells(I, LC + 1) = Mid(WkStg, StNb, EndNb - StNb)
                    J = EndNb
                Else
                    SFlg = False
                End If
            End With
        Wend
    Next I
    Application.ScreenUpdating = True
    MsgBox (" Job Done")
End Sub

Display More

Arimateia · Oct 25th 2017

Hello
Follow the link that can help
https://trumpexcel.com/split-multiple-lines/

Arimateia · Oct 25th 2017

Hello
Follow the link that can help
https://trumpexcel.com/split-multiple-lines/

Arimateia · Oct 25th 2017

This link can help

https://trumpexcel.com/split-multiple-lines/

formula to extract strings containing the keyword "upset" between DATE and ENDNOTE

Participate now!

Share