Forum Discussion
Find a word in a cell from a list of words
- Jun 19, 2018
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:
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
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!
- Haytham AmairahJun 19, 2018Silver Contributor
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.