Find a word in a cell from a list of words

[alex alex]

Hi,

Looking for an advice.

I have a list of words in a column and sentence in a cell.

What would be the formula, to search for these words in a sentence and give a result of matching word in a separate cell?

 

[#2]

Hi Alex,

You can depend on a formula like this:

=INDEX($B$2:$B$4,MATCH("*"&B2&"*",$A$2:$A$4,0))

 

Please find it in the attached file

Regards

[#3]

Hi Haytham,

Thank you for your help!

Unfortunately the formula doesn't work when valid value not found or when B cell is empty.

Here's a real example of the spreadsheet I'm working on. Column A - product titles, column B is names of all possible gemstones. I'm trying to extract gemstone names from a title. About 4000 product lines to go through.

Thank you in advance!

[#4]

Sorry, but you cannot extract the name of the gemstone based on its description.

Because you don't have any pattern or lookup table you can depend on.

You need to restructure this data so that you get a table contained the name of the gemstone and its characteristics like this:

Gemstone Name	Specific Gravity	Mohs Hardness	Index Refraction	Gemstone Family
Achorite	3.03 - 3.25	7.0 - 7.5 Mohs	1.610 - 1.661	Tourmaline
Agate	2.65	7.0 Mohs	1.530 - 1.550	Quartz
Alexandrite	3.68 - 3.78	8.5 Mohs	1.746 - 1.755	Chrysoberyl
Almandine	3.50 - 4.30	6.5 - 7.5 Mohs	1.750 - 1.830	Garnet
Almandine Spinel	3.58 - 4.06	8.0 Mohs	1.708 - 1.735	Spinel
Almandine-Spessartine	3.50 - 4.30	6.5 - 7.5 Mohs	1.750 - 1.830	Garnet
Amazonite	2.55 - 2.76	6.0 - 6.5 Mohs	1.518 - 1.526	Feldspar
Amethyst	2.65	7.0 Mohs	1.532 - 1.554	Quartz
Andradite	3.50 - 4.30	6.5 - 7.5 Mohs	1.880 - 1.940	Garnet
Aquamarine	2.63 - 2.91	7.5 - 8.0 Mohs	1.567 - 1.590	Beryl
Balas Ruby	3.58 - 4.06	8.0 Mohs	1.708 - 1.735	Spinel
Black Opal	1.98 - 2.25	5.5 - 6.5 Mohs	1.440 - 1.460	Opal
Bloodstone	2.65	7.0 Mohs	1.535 - 1.539	Quartz
Brazilian Emerald	3.03 - 3.25	7.0 - 7.5 Mohs	1.610 - 1.640	Tourmaline
Cairngorm	2.65	7.0 Mohs	1.540 - 1.550	Quartz
Carnelian	2.65	7.0 Mohs	1.530 - 1.550	Quartz
Cat's Eye	3.68 - 3.78	8.5 Mohs	1.746 - 1.755	Chrysoberyl
Chalcedony	2.65	7.0 Mohs	1.544 - 1.553	Quartz
Chlorspinel	3.58 - 4.06	8.0 Mohs	1.712 - 1.717	Spinel
Chrome Pyrope	3.50 - 4.30	6.5 - 7.5 Mohs	1.730 - 1.760	Garnet
Chrysoberyl	3.68 - 3.78	8.5 Mohs	1.746 - 1.755	Chrysoberyl
Chrysoprase	2.65	7.0 Mohs	1.530 - 1.550	Quartz
Citrine	2.65	7.0 Mohs	1.532 - 1.554	Quartz
Color-Change Garnet	3.50 - 4.30	6.5 - 7.5 Mohs	1.730 - 1.760	Garnet
Demantoid	3.50 - 4.30	6.5 - 7.5 Mohs	1.880 - 1.900	Garnet
Diamond	3.51	10 Mohs	2.417	Diamond
Dravite	3.03 - 3.25	7.0 - 7.5 Mohs	1.610-1.661	Tourmaline
Emerald	2.63 - 2.91	7.5 - 8.0 Mohs	1.560 - 1.605	Beryl
Feldspar	2.55 - 2.76	6.0 - 6.5 Mohs	1.518 - 1.572	Feldspar
Fire Opal	1.98 - 2.25	5.5 - 6.5 Mohs	1.430 - 1.460	Opal

After that, you can depend on this table to lookup the information you need.

[#5 · Accepted solution]

Alex-

Maybe try using this macro.  Since I cannot post a macro enabled workbook due to security risks.  You'll need to insert this code in a standard code module for it to work:

VBA:

Sub GetWords()

    Dim wrdLRow As Integer
    Dim wrdLp As Integer
    Dim CommentLrow As Integer
    Dim CommentLp As Integer
    Dim fndWord As Integer
    Dim Sht As Worksheet
    
    On Error Resume Next 'Suppress Errors... for when we don't find a match
    
    'Define worksheet that has data on it....
    Set Sht = Sheets("Sheet1")
    
    'Get last row for words based on column A
    wrdLRow = Sht.Cells(Rows.Count, "A").End(xlUp).Row
    
    'Get last row for comments based on column C
    CommentLrow = Sht.Cells(Rows.Count, "C").End(xlUp).Row
    
    'Loop through lists and find matches....
    For CommentLp = 2 To CommentLrow
    
        For wrdLp = 2 To wrdLRow

            'Look for word...
            fndWord = Application.WorksheetFunction.Search(Sht.Cells(wrdLp, "A"), Sht.Cells(CommentLp, "C"))
            
            'If we found the word....then
            If fndWord > 0 Then
                Sht.Cells(CommentLp, "D") = Sht.Cells(CommentLp, "D") & "; " & Sht.Cells(wrdLp, "A")
                fndWord = 0 'Reset Variable for next loop
            End If
            
        Next wrdLp
        
        Sht.Cells(CommentLp, "D") = Mid(Sht.Cells(CommentLp, "D"), 3, Len(Sht.Cells(CommentLp, "D")) - 2)

    Next CommentLp

End Sub

BEFORE:

AFTER:

[#6]

Matt,

I came across your code today and it works wonderfully. How would the code need to be changed if I had the list of words on another sheet?

Thanks in advance for any assistance.

[#7]

I needed this as a solution as well. I had over 16,000 rows of data that I needed to pull one word out of, worked perfectly. Thank you for sharing!

[#8]

@Gaminggeek I am going to try using a named range to see if that will work with the list being on a different sheet.

[#9]

@Matt Mickle 

This is great but is there a way of outputting any words which in a search/comment that are not part of a list?

So for example if you have  a list of 'yes', 'love', 'no' and the comment is 'Yes I love my job', the VBA would output the words 'I' , 'my' , 'job'

Any help would be much appreciated

[#10]

hi, its working great, but there's one prob with it...

suppose the string in col C is "How can i make excel to search word against a list of words?"
and the words to look in col A is "how, ke, cel, abcd, xyz"
then this will return the "how; ke; cel;",

but i want it to only return "how" not the char "ke;cel;" inside the words "make;excel"
how to modify this script, so, it returns only words if it contains space either before of after the word, so it can be guessed as the whole word insted of char.

[#11]

@alex alex You can use the following formula in Column E to find the word from Column C in the cell in Column A.

={IFERROR(INDEX(LOWER(LIST),MATCH(0,0*FIND(IF(ISNA(MATCH(LOWER(LIST),$A$2:$A$4,0)),LOWER(LIST)),$A4),0)),"-")}

To get the curly brackets { } around your formula you need to use Ctrl Shift-Enter  to perform the calculations with array formulae.

Screenshot and example file attached.

[#12]

@jjwprotozoa Thank you for your formula. Can you please help me i have attached a sheet i m not getting the exact match of the ID. 

[#13]

@alex alex 

You don't always have to invent something that already exists ... only when it is necessary ... otherwise the existing invention will also work.

Find and select cells that meet specific conditions

(click on the link for more infos)

Is just a simple thought from someone who doesn't know anything, if the thought is uninteresting, please just ignore it.

Thank you all for your understanding and patience

Nikolino

I know I don't know anything (Socrates)

[#14]

Hello Mr. Matt,

there's a problem if it finds a part of the word it considers it a match,

For instance, the word WTS21HHBK1 was in a sentence and when I searched for it in a list the outcome was ; WTS21HHBK; WTS21HHBK1 (both are in the list but only one in the sentence) how can I overcome this problem?

[#15]

Hello Haytham,

I tried the formula you recommended to Alex and it did not work.  Can you tell me what I did wrong?

Thank you.