SOLVED

Find a word in a cell from a list of words

Copper Contributor

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?

 Untitled2.jpg

 

14 Replies

Hi Alex,

 

You can depend on a formula like this:

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

Partial Text Match.png 

Please find it in the attached file

Regards

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!


Untitled4.jpg

 

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 NameSpecific GravityMohs HardnessIndex RefractionGemstone Family
Achorite3.03 - 3.257.0 - 7.5 Mohs1.610 - 1.661Tourmaline
Agate2.657.0 Mohs1.530 - 1.550Quartz
Alexandrite3.68 - 3.788.5 Mohs1.746 - 1.755Chrysoberyl
Almandine3.50 - 4.306.5 - 7.5 Mohs1.750 - 1.830Garnet
Almandine Spinel3.58 - 4.068.0 Mohs1.708 - 1.735Spinel
Almandine-Spessartine3.50 - 4.306.5 - 7.5 Mohs1.750 - 1.830Garnet
Amazonite2.55 - 2.766.0 - 6.5 Mohs1.518 - 1.526Feldspar
Amethyst2.657.0 Mohs1.532 - 1.554Quartz
Andradite3.50 - 4.306.5 - 7.5 Mohs1.880 - 1.940Garnet
Aquamarine2.63 - 2.917.5 - 8.0 Mohs1.567 - 1.590Beryl
Balas Ruby3.58 - 4.068.0 Mohs1.708 - 1.735Spinel
Black Opal1.98 - 2.255.5 - 6.5 Mohs1.440 - 1.460Opal
Bloodstone2.657.0 Mohs1.535 - 1.539Quartz
Brazilian Emerald3.03 - 3.257.0 - 7.5 Mohs1.610 - 1.640Tourmaline
Cairngorm2.657.0 Mohs1.540 - 1.550Quartz
Carnelian2.657.0 Mohs1.530 - 1.550Quartz
Cat's Eye3.68 - 3.788.5 Mohs1.746 - 1.755Chrysoberyl
Chalcedony2.657.0 Mohs1.544 - 1.553Quartz
Chlorspinel3.58 - 4.068.0 Mohs1.712 - 1.717Spinel
Chrome Pyrope3.50 - 4.306.5 - 7.5 Mohs1.730 - 1.760Garnet
Chrysoberyl3.68 - 3.788.5 Mohs1.746 - 1.755Chrysoberyl
Chrysoprase2.657.0 Mohs1.530 - 1.550Quartz
Citrine2.657.0 Mohs1.532 - 1.554Quartz
Color-Change Garnet3.50 - 4.306.5 - 7.5 Mohs1.730 - 1.760Garnet
Demantoid3.50 - 4.306.5 - 7.5 Mohs1.880 - 1.900Garnet
Diamond3.5110 Mohs2.417Diamond
Dravite3.03 - 3.257.0 - 7.5 Mohs1.610-1.661Tourmaline
Emerald2.63 - 2.917.5 - 8.0 Mohs1.560 - 1.605Beryl
Feldspar2.55 - 2.766.0 - 6.5 Mohs1.518 - 1.572Feldspar
Fire Opal1.98 - 2.255.5 - 6.5 Mohs1.430 - 1.460Opal

 

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

best response confirmed by alex alex (Copper Contributor)
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:

SentimentBefore.png

AFTER:

SentimentAfter.png

 

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.

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!

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

@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

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.

@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.

@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. 

@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)

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?

Hello Haytham,

 

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

Jeff_B1973_0-1704322527074.png

 

Thank you.

1 best response

Accepted Solutions
best response confirmed by alex alex (Copper Contributor)
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:

SentimentBefore.png

AFTER:

SentimentAfter.png

 

View solution in original post