Extracting Words from Paragraph Excel cell that match a list of Possible words from list

Hello, I am trying to extract locations from 5,000 projects that I have in Excel 2013. In the cell is a 2-4 paragraph abstract from the project and within our locations. I compiled a list of all locations I thought would be possible which is a list of about 916. Problem is that I have no idea how to do this...
I was able to extract 1 word into adjacent cell using

=IFERROR(LOOKUP(1E+100,SEARCH(places[Column1],F23),places[Column1]),"")
where places[column1] is the list of 916 locations and Column if is the column of Research abstracts. I have been trying all week and this is my first forum post. Any help would be appreciated. Thanks.

joshHammes welcome to the forum.

Since I don't know where you want output in relation to the source data I output to the same row. This can be changed to suit. The formula is array entered and filled across. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

=IFERROR(INDEX(places,SMALL(IF(ISNUMBER(SEARCH(places,$A$1)),ROW(places)-MIN(ROW(places))+1),COLUMNS($B:B))),"")

	A	B	C	D	E	F
1	Now is the time for all good men to come to the aid of their country.	country	aid	time

Unfortunately, all that did when I pasted it in my workbook was pull the names of the places in the order they appeared on the list. It is almost as if it did not even read the cell with the paragraph in it.

You hadn't mentioned this part.

I'm not encouraged. From the looks of your screen shot a formula solution is impractical even for the formula I posted. The only "solution" I am likely to come up with will be a lengthy array formula. If you are not aware of it array formulas are resource heavy.

Perhaps someone can come up with a VBA solution. Unfortunately I don't know enough VBA.

Edit I'll see if I can roust up some specialist(s).

Originally Posted by joshHammes

okay thank you, in plain-English, can you tell me what your code does exactly?

If you are asking about formula that will return the locations in the string:

• Parse the string in memory (an array).
• Use MATCH or SEARCH using places for the list of lookup values.
• That returns another array.
• Assign index numbers to those returns (another array)
• Group the index numbers with SMALL.
• Pass the indexed numbers to the INDEX first argument (the parsed array. Calculated yet a second time with all its arrays.)
• Return what it finds.

Provide a sample workbook with desired results:
(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

I was able to do another array formula without parsing. With just the 22 rows and 13 columns of output (there could be more columns) it took 52+ seconds.

FWIW here is the array formula I applied. You don't want to use this.

Formula:

=IFERROR(INDEX(INDEX(places,N(IF(1,MATCH(SMALL(IF(ISNUMBER(FIND(LOWER(places),LOWER($F1))),

(FIND(LOWER(places),LOWER($F1)))+ROW(places)/10^5),ROW(INDEX(A:A,1):INDEX(A:A,COUNT((FIND(LOWER(places),LOWER($F1))))))),

IF(ISNUMBER(FIND(LOWER(places),LOWER($F1))),(FIND(LOWER(places),LOWER($F1)))+ROW(places)/10^5),0)))),COLUMNS($B:B)),"")

You're welcome.

My thoughts were if I could run the same formula on the adjacent cell and tell it that the value could not equal the value from the previous cell then I would be good to go.. Any thoughts?? thanks again.

I believe that would be impractical.

I am still waiting to see if VBA solution(s) are proposed.

There has to be a better way.

Search column F and write a string of found locations in column G for each F cell

Option Explicit

Sub Macro1()
    Dim PlacesList      As Variant, _
        TestBlock       As Range, _
        Descriptions    As Long, _
        LastBlock       As Long, _
        Results         As String, _
        Loc             As Variant, _
        t0              As Double, _
        matches         As Long
        
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With 'application
    
    LastBlock = Cells(Rows.Count, "F").End(xlUp).Row
    
    PlacesList = WorksheetFunction.Transpose(Range("places").Value)
    t0 = Timer
    
    For Each TestBlock In Range("F1").Resize(rowsize:=LastBlock)
        'clear the holder string
        Results = ""
        
        'search for each possible place
        For Each Loc In PlacesList
        
            'check if it is followed by a space or period
            If InStr(TestBlock, Loc & " ") > 0 Or InStr(TestBlock, Loc & ".") Then
                matches = matches + 1
                If InStr(Results, Loc) = 0 Then
                
                    'if found, append to the string
                    Results = Results & IIf(Results = "", "", ",") & Loc
                End If
            End If
        Next Loc
        
        'write the found locations to column G
        TestBlock.Offset(columnoffset:=1).Value = Results
    Next TestBlock
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With 'application
    
    'report process time
    MsgBox "Process took: " & Timer - t0 & IIf((Timer - t0) > 1, " secs ", " sec ") & vbCrLf & "with " & matches & " matches found"
End Sub

Originally Posted by protonLeah

Search column F and write a string of found locations in column G for each F cell

Option Explicit

Sub Macro1()
    Dim PlacesList      As Variant, _
        TestBlock       As Range, _
        Descriptions    As Long, _
        LastBlock       As Long, _
        Results         As String, _
        Loc             As Variant, _
        t0              As Double, _
        matches         As Long
        
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With 'application
    
    LastBlock = Cells(Rows.Count, "F").End(xlUp).Row
    
    PlacesList = WorksheetFunction.Transpose(Range("places").Value)
    t0 = Timer
    
    For Each TestBlock In Range("F1").Resize(rowsize:=LastBlock)
        'clear the holder string
        Results = ""
        
        'search for each possible place
        For Each Loc In PlacesList
        
            'check if it is followed by a space or period
            If InStr(TestBlock, Loc & " ") > 0 Or InStr(TestBlock, Loc & ".") Then
                matches = matches + 1
                If InStr(Results, Loc) = 0 Then
                
                    'if found, append to the string
                    Results = Results & IIf(Results = "", "", ",") & Loc
                End If
            End If
        Next Loc
        
        'write the found locations to column G
        TestBlock.Offset(columnoffset:=1).Value = Results
    Next TestBlock
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With 'application
    
    'report process time
    MsgBox "Process took: " & Timer - t0 & IIf((Timer - t0) > 1, " secs ", " sec ") & vbCrLf & "with " & matches & " matches found"
End Sub

Hello Leah, Thank you very much! You are a lifesaver!

@ joshHammes

Glad you found it.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.