SOLVED
Home

Find a word in a cell from a list of words

%3CLINGO-SUB%20id%3D%22lingo-sub-205218%22%20slang%3D%22en-US%22%3EFind%20a%20word%20in%20a%20cell%20from%20a%20list%20of%20words%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-205218%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3ELooking%20for%20an%20advice.%3C%2FP%3E%3CP%3EI%20have%20a%20list%20of%20words%20in%20a%20column%20and%20sentence%20in%20a%20cell.%3C%2FP%3E%3CP%3EWhat%20would%20be%20the%20formula%2C%20to%20search%20for%20these%20words%20in%20a%20sentence%20and%20give%20a%20result%20of%20matching%20word%20in%20a%20separate%20cell%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20388px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F36181iD5E6359A0785B7FC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Untitled2.jpg%22%20title%3D%22Untitled2.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-205218%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Desktop%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359879%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20a%20word%20in%20a%20cell%20from%20a%20list%20of%20words%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359879%22%20slang%3D%22en-US%22%3E%3CP%3EMatt%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20came%20across%20your%20code%20today%20and%20it%20works%20wonderfully.%20How%20would%20the%20code%20need%20to%20be%20changed%20if%20I%20had%20the%20list%20of%20words%20on%20another%20sheet%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20any%20assistance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-205762%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20a%20word%20in%20a%20cell%20from%20a%20list%20of%20words%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-205762%22%20slang%3D%22en-US%22%3E%3CP%3EAlex-%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMaybe%20try%20using%20this%20macro.%26nbsp%3B%20Since%20I%20cannot%20post%20a%20macro%20enabled%20workbook%20due%20to%20security%20risks.%26nbsp%3B%20You'll%20need%20to%20insert%20this%20code%20in%20a%20standard%20code%20module%20for%20it%20to%20work%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EVBA%3A%3C%2FP%3E%0A%3CPRE%3ESub%20GetWords()%0A%0A%20%20%20%20Dim%20wrdLRow%20As%20Integer%0A%20%20%20%20Dim%20wrdLp%20As%20Integer%0A%20%20%20%20Dim%20CommentLrow%20As%20Integer%0A%20%20%20%20Dim%20CommentLp%20As%20Integer%0A%20%20%20%20Dim%20fndWord%20As%20Integer%0A%20%20%20%20Dim%20Sht%20As%20Worksheet%0A%20%20%20%20%0A%20%20%20%20On%20Error%20Resume%20Next%20'Suppress%20Errors...%20for%20when%20we%20don't%20find%20a%20match%0A%20%20%20%20%0A%20%20%20%20'Define%20worksheet%20that%20has%20data%20on%20it....%0A%20%20%20%20Set%20Sht%20%3D%20Sheets(%22Sheet1%22)%0A%20%20%20%20%0A%20%20%20%20'Get%20last%20row%20for%20words%20based%20on%20column%20A%0A%20%20%20%20wrdLRow%20%3D%20Sht.Cells(Rows.Count%2C%20%22A%22).End(xlUp).Row%0A%20%20%20%20%0A%20%20%20%20'Get%20last%20row%20for%20comments%20based%20on%20column%20C%0A%20%20%20%20CommentLrow%20%3D%20Sht.Cells(Rows.Count%2C%20%22C%22).End(xlUp).Row%0A%20%20%20%20%0A%20%20%20%20'Loop%20through%20lists%20and%20find%20matches....%0A%20%20%20%20For%20CommentLp%20%3D%202%20To%20CommentLrow%0A%20%20%20%20%0A%20%20%20%20%20%20%20%20For%20wrdLp%20%3D%202%20To%20wrdLRow%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20'Look%20for%20word...%0A%20%20%20%20%20%20%20%20%20%20%20%20fndWord%20%3D%20Application.WorksheetFunction.Search(Sht.Cells(wrdLp%2C%20%22A%22)%2C%20Sht.Cells(CommentLp%2C%20%22C%22))%0A%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20'If%20we%20found%20the%20word....then%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20fndWord%20%26gt%3B%200%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Sht.Cells(CommentLp%2C%20%22D%22)%20%3D%20Sht.Cells(CommentLp%2C%20%22D%22)%20%26amp%3B%20%22%3B%20%22%20%26amp%3B%20Sht.Cells(wrdLp%2C%20%22A%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20fndWord%20%3D%200%20'Reset%20Variable%20for%20next%20loop%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20Next%20wrdLp%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20Sht.Cells(CommentLp%2C%20%22D%22)%20%3D%20Mid(Sht.Cells(CommentLp%2C%20%22D%22)%2C%203%2C%20Len(Sht.Cells(CommentLp%2C%20%22D%22))%20-%202)%0A%0A%20%20%20%20Next%20CommentLp%0A%0AEnd%20Sub%0A%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBEFORE%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20939px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F36278i53D418E51191D7B1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22SentimentBefore.png%22%20title%3D%22SentimentBefore.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EAFTER%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20842px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F36280i51A8FF03FE6BCDD0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22SentimentAfter.png%22%20title%3D%22SentimentAfter.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-205744%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20a%20word%20in%20a%20cell%20from%20a%20list%20of%20words%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-205744%22%20slang%3D%22en-US%22%3E%3CP%3ESorry%2C%20but%20you%20cannot%20extract%20the%20name%20of%20the%20gemstone%20based%20on%20its%20description.%3C%2FP%3E%3CP%3EBecause%20you%20don't%20have%20any%20pattern%20or%20lookup%20table%20you%20can%20depend%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20need%20to%20restructure%20this%20data%20so%20that%20you%20get%20a%20table%20contained%20the%20name%20of%20the%20gemstone%20and%20its%20characteristics%20like%20this%3A%3C%2FP%3E%3CTABLE%20cellspacing%3D%220%22%20cellpadding%3D%220%22%20border%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3EGemstone%20Name%3C%2FTD%3E%3CTD%3ESpecific%20Gravity%3C%2FTD%3E%3CTD%3EMohs%20Hardness%3C%2FTD%3E%3CTD%3EIndex%20Refraction%3C%2FTD%3E%3CTD%3EGemstone%20Family%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAchorite%3C%2FTD%3E%3CTD%3E3.03%20-%203.25%3C%2FTD%3E%3CTD%3E7.0%20-%207.5%20Mohs%3C%2FTD%3E%3CTD%3E1.610%20-%201.661%3C%2FTD%3E%3CTD%3ETourmaline%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAgate%3C%2FTD%3E%3CTD%3E2.65%3C%2FTD%3E%3CTD%3E7.0%20Mohs%3C%2FTD%3E%3CTD%3E1.530%20-%201.550%3C%2FTD%3E%3CTD%3EQuartz%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAlexandrite%3C%2FTD%3E%3CTD%3E3.68%20-%203.78%3C%2FTD%3E%3CTD%3E8.5%20Mohs%3C%2FTD%3E%3CTD%3E1.746%20-%201.755%3C%2FTD%3E%3CTD%3EChrysoberyl%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAlmandine%3C%2FTD%3E%3CTD%3E3.50%20-%204.30%3C%2FTD%3E%3CTD%3E6.5%20-%207.5%20Mohs%3C%2FTD%3E%3CTD%3E1.750%20-%201.830%3C%2FTD%3E%3CTD%3EGarnet%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAlmandine%20Spinel%3C%2FTD%3E%3CTD%3E3.58%20-%204.06%3C%2FTD%3E%3CTD%3E8.0%20Mohs%3C%2FTD%3E%3CTD%3E1.708%20-%201.735%3C%2FTD%3E%3CTD%3ESpinel%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAlmandine-Spessartine%3C%2FTD%3E%3CTD%3E3.50%20-%204.30%3C%2FTD%3E%3CTD%3E6.5%20-%207.5%20Mohs%3C%2FTD%3E%3CTD%3E1.750%20-%201.830%3C%2FTD%3E%3CTD%3EGarnet%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAmazonite%3C%2FTD%3E%3CTD%3E2.55%20-%202.76%3C%2FTD%3E%3CTD%3E6.0%20-%206.5%20Mohs%3C%2FTD%3E%3CTD%3E1.518%20-%201.526%3C%2FTD%3E%3CTD%3EFeldspar%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAmethyst%3C%2FTD%3E%3CTD%3E2.65%3C%2FTD%3E%3CTD%3E7.0%20Mohs%3C%2FTD%3E%3CTD%3E1.532%20-%201.554%3C%2FTD%3E%3CTD%3EQuartz%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAndradite%3C%2FTD%3E%3CTD%3E3.50%20-%204.30%3C%2FTD%3E%3CTD%3E6.5%20-%207.5%20Mohs%3C%2FTD%3E%3CTD%3E1.880%20-%201.940%3C%2FTD%3E%3CTD%3EGarnet%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAquamarine%3C%2FTD%3E%3CTD%3E2.63%20-%202.91%3C%2FTD%3E%3CTD%3E7.5%20-%208.0%20Mohs%3C%2FTD%3E%3CTD%3E1.567%20-%201.590%3C%2FTD%3E%3CTD%3EBeryl%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBalas%20Ruby%3C%2FTD%3E%3CTD%3E3.58%20-%204.06%3C%2FTD%3E%3CTD%3E8.0%20Mohs%3C%2FTD%3E%3CTD%3E1.708%20-%201.735%3C%2FTD%3E%3CTD%3ESpinel%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBlack%20Opal%3C%2FTD%3E%3CTD%3E1.98%20-%202.25%3C%2FTD%3E%3CTD%3E5.5%20-%206.5%20Mohs%3C%2FTD%3E%3CTD%3E1.440%20-%201.460%3C%2FTD%3E%3CTD%3EOpal%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBloodstone%3C%2FTD%3E%3CTD%3E2.65%3C%2FTD%3E%3CTD%3E7.0%20Mohs%3C%2FTD%3E%3CTD%3E1.535%20-%201.539%3C%2FTD%3E%3CTD%3EQuartz%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBrazilian%20Emerald%3C%2FTD%3E%3CTD%3E3.03%20-%203.25%3C%2FTD%3E%3CTD%3E7.0%20-%207.5%20Mohs%3C%2FTD%3E%3CTD%3E1.610%20-%201.640%3C%2FTD%3E%3CTD%3ETourmaline%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECairngorm%3C%2FTD%3E%3CTD%3E2.65%3C%2FTD%3E%3CTD%3E7.0%20Mohs%3C%2FTD%3E%3CTD%3E1.540%20-%201.550%3C%2FTD%3E%3CTD%3EQuartz%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECarnelian%3C%2FTD%3E%3CTD%3E2.65%3C%2FTD%3E%3CTD%3E7.0%20Mohs%3C%2FTD%3E%3CTD%3E1.530%20-%201.550%3C%2FTD%3E%3CTD%3EQuartz%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECat's%20Eye%3C%2FTD%3E%3CTD%3E3.68%20-%203.78%3C%2FTD%3E%3CTD%3E8.5%20Mohs%3C%2FTD%3E%3CTD%3E1.746%20-%201.755%3C%2FTD%3E%3CTD%3EChrysoberyl%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EChalcedony%3C%2FTD%3E%3CTD%3E2.65%3C%2FTD%3E%3CTD%3E7.0%20Mohs%3C%2FTD%3E%3CTD%3E1.544%20-%201.553%3C%2FTD%3E%3CTD%3EQuartz%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EChlorspinel%3C%2FTD%3E%3CTD%3E3.58%20-%204.06%3C%2FTD%3E%3CTD%3E8.0%20Mohs%3C%2FTD%3E%3CTD%3E1.712%20-%201.717%3C%2FTD%3E%3CTD%3ESpinel%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EChrome%20Pyrope%3C%2FTD%3E%3CTD%3E3.50%20-%204.30%3C%2FTD%3E%3CTD%3E6.5%20-%207.5%20Mohs%3C%2FTD%3E%3CTD%3E1.730%20-%201.760%3C%2FTD%3E%3CTD%3EGarnet%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EChrysoberyl%3C%2FTD%3E%3CTD%3E3.68%20-%203.78%3C%2FTD%3E%3CTD%3E8.5%20Mohs%3C%2FTD%3E%3CTD%3E1.746%20-%201.755%3C%2FTD%3E%3CTD%3EChrysoberyl%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EChrysoprase%3C%2FTD%3E%3CTD%3E2.65%3C%2FTD%3E%3CTD%3E7.0%20Mohs%3C%2FTD%3E%3CTD%3E1.530%20-%201.550%3C%2FTD%3E%3CTD%3EQuartz%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECitrine%3C%2FTD%3E%3CTD%3E2.65%3C%2FTD%3E%3CTD%3E7.0%20Mohs%3C%2FTD%3E%3CTD%3E1.532%20-%201.554%3C%2FTD%3E%3CTD%3EQuartz%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EColor-Change%20Garnet%3C%2FTD%3E%3CTD%3E3.50%20-%204.30%3C%2FTD%3E%3CTD%3E6.5%20-%207.5%20Mohs%3C%2FTD%3E%3CTD%3E1.730%20-%201.760%3C%2FTD%3E%3CTD%3EGarnet%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDemantoid%3C%2FTD%3E%3CTD%3E3.50%20-%204.30%3C%2FTD%3E%3CTD%3E6.5%20-%207.5%20Mohs%3C%2FTD%3E%3CTD%3E1.880%20-%201.900%3C%2FTD%3E%3CTD%3EGarnet%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDiamond%3C%2FTD%3E%3CTD%3E3.51%3C%2FTD%3E%3CTD%3E10%20Mohs%3C%2FTD%3E%3CTD%3E2.417%3C%2FTD%3E%3CTD%3EDiamond%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDravite%3C%2FTD%3E%3CTD%3E3.03%20-%203.25%3C%2FTD%3E%3CTD%3E7.0%20-%207.5%20Mohs%3C%2FTD%3E%3CTD%3E1.610-1.661%3C%2FTD%3E%3CTD%3ETourmaline%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEmerald%3C%2FTD%3E%3CTD%3E2.63%20-%202.91%3C%2FTD%3E%3CTD%3E7.5%20-%208.0%20Mohs%3C%2FTD%3E%3CTD%3E1.560%20-%201.605%3C%2FTD%3E%3CTD%3EBeryl%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFeldspar%3C%2FTD%3E%3CTD%3E2.55%20-%202.76%3C%2FTD%3E%3CTD%3E6.0%20-%206.5%20Mohs%3C%2FTD%3E%3CTD%3E1.518%20-%201.572%3C%2FTD%3E%3CTD%3EFeldspar%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFire%20Opal%3C%2FTD%3E%3CTD%3E1.98%20-%202.25%3C%2FTD%3E%3CTD%3E5.5%20-%206.5%20Mohs%3C%2FTD%3E%3CTD%3E1.430%20-%201.460%3C%2FTD%3E%3CTD%3EOpal%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20that%2C%20you%20can%20depend%20on%20this%20table%20to%20lookup%20the%20information%20you%20need.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-205322%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20a%20word%20in%20a%20cell%20from%20a%20list%20of%20words%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-205322%22%20slang%3D%22en-US%22%3E%3CDIV%3E%3CP%3EHi%26nbsp%3BHaytham%2C%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help!%3C%2FP%3E%3CP%3EUnfortunately%20the%20formula%20doesn't%20work%20when%20valid%20value%20not%20found%20or%20when%20B%20cell%20is%20empty.%3C%2FP%3E%3CP%3EHere's%20a%20real%20example%20of%20the%20spreadsheet%20I'm%20working%20on.%20Column%20A%20-%20product%20titles%2C%20column%20B%20is%20names%20of%20all%20possible%20gemstones.%20I'm%20trying%20to%20extract%26nbsp%3Bgemstone%20names%20from%20a%20title.%26nbsp%3BAbout%204000%20product%20lines%20to%20go%20through.%3C%2FP%3E%3CP%3EThank%20you%20in%20advance!%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20561px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F36197i0F7021DAA9F4985B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Untitled4.jpg%22%20title%3D%22Untitled4.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-205233%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20a%20word%20in%20a%20cell%20from%20a%20list%20of%20words%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-205233%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Alex%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20depend%20on%20a%20formula%20like%20this%3A%3C%2FP%3E%3CPRE%3E%3DINDEX(%24B%242%3A%24B%244%2CMATCH(%22*%22%26amp%3BB2%26amp%3B%22*%22%2C%24A%242%3A%24A%244%2C0))%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F36185i80DD6E1F164D3994%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Partial%20Text%20Match.png%22%20title%3D%22Partial%20Text%20Match.png%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20it%20in%20the%20attached%20file%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-467812%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20a%20word%20in%20a%20cell%20from%20a%20list%20of%20words%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-467812%22%20slang%3D%22en-US%22%3EI%20needed%20this%20as%20a%20solution%20as%20well.%20I%20had%20over%2016%2C000%20rows%20of%20data%20that%20I%20needed%20to%20pull%20one%20word%20out%20of%2C%20worked%20perfectly.%20Thank%20you%20for%20sharing!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-510710%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20a%20word%20in%20a%20cell%20from%20a%20list%20of%20words%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-510710%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F294905%22%20target%3D%22_blank%22%3E%40Gaminggeek%3C%2FA%3E%26nbsp%3BI%20am%20going%20to%20try%20using%20a%20named%20range%20to%20see%20if%20that%20will%20work%20with%20the%20list%20being%20on%20a%20different%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-754138%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20a%20word%20in%20a%20cell%20from%20a%20list%20of%20words%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-754138%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F37127%22%20target%3D%22_blank%22%3E%40Matt%20Mickle%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20great%20but%20is%20there%20a%20way%20of%20outputting%20any%20words%20which%20in%20a%20search%2Fcomment%20that%20are%20not%20part%20of%20a%20list%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20for%20example%20if%20you%20have%26nbsp%3B%20a%20list%20of%20'yes'%2C%20'love'%2C%20'no'%20and%20the%20comment%20is%20'Yes%20I%20love%20my%20job'%2C%20the%20VBA%20would%20output%20the%20words%20'I'%20%2C%20'my'%20%2C%20'job'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20much%20appreciated%3C%2FP%3E%3C%2FLINGO-BODY%3E
alex alex
New 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

 

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

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

Related Conversations