Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Find Word(s) Within Text String That Matches A List Of Keywords

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi Everyone,

This is my first post.

Although the solution I seek seems rather straightforward, i'm beginning to wonder if it's even technically possible since I can't find a formula that works - using lookup, find, or a combination of various functions.

Basically, I have a column of text strings in worksheet one which I need to check for the presence of keywords which is listed in another column in worksheet two (the keyword list).

So if any word from the keyword list is found in, say, cell A1 of sheet one, the cell to its right (B1) should have a formula to display either the first match (or if possible, all the matched words).

Factors to be taken into consideration a
- the column of text in sheet one may be run for up to 500 rows
- the keyword list may contain up to 3000 keywords (3000 rows)
- the match must to be case sensitive
- the match must be for whole words (and not match "office" to "off")
- there are punctuations in the text in sheet one.

I have also attached an example of how my data is organised.

Thanks!

View Answers     

Similar Excel Tutorials

Delete Entire Rows Based on Predefined Criteria (Text)
This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will c ...
Count the Number of Cells that Start or End with Specific Text in Excel
How to count cells that match text at the start or the end of a string in Excel. If you want a fuller explanation o ...
Extract the First Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
This free Excel UDF (user defined function) returns the first word from a cell in Excel. This extracts the first w ...
Get the First Word from a Cell in Excel
How to use a formula to get the first word from a cell in Excel. This works for a single cell and an entire list or ...

Helpful Excel Macros

Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi

Similar Topics









Hello,

Previously a poster requested a solution to finding text within a string, given a list of keywords.

Here is a link to the original thread: http://www.excelforum.com/excel-gene...-keywords.html

Relevant text:
Basically, I have a column of text strings in worksheet one which I need to check for the presence of keywords which is listed in another column in worksheet two (the keyword list).

So if any word from the keyword list is found in, say, cell A1 of sheet one, the cell to its right (B1) should have a formula to display either the first match (or if possible, all the matched words).

Factors to be taken into consideration a
- the column of text in sheet one may be run for up to 500 rows
- the keyword list may contain up to 3000 keywords (3000 rows)
- the match must to be case sensitive
- the match must be for whole words (and not match "office" to "off")
- there are punctuations in the text in sheet one.

Original Solution: find word(s) within text string that matches a list of keywords.xls

This works perfectly.

However, it is case sensitive. Two questions:

1. Is it possible to modify the formula to allow a match to be made if the data in A1 does NOT match the case (upper/lower/mixed) of the keywords in the list?
2. Can the keyword(s) extracted from A1 be in the same case as the Keyword list (not the data in A1)?

I have spent the better part of a day searching for the answer to this and trying many different approaches. My knowledge of Excel is just enough to know that there is a lot I don't know!

Thanks,

gbm

Hi, I'm trying to find an Excel formula or array, etc. to be able to do the following:

I have a column in a spreadsheet with about 50 text keywords/phrases (one keyword/phrase on each row) and I need to find which rows of article text contain one or more of these keywords. Then I have rows in the spreadsheet that contain the text content for articles with the following column headings:

Column A is the keyword list
Column B is the Article Title
Column C is the Author's Name
Column D is the Article Text
Column E is the Article Publication Date.

Each row in the spreadsheet contains the content for one article so I need the formula to check to see how many (if any) of the column A list of keywords exist in the article text cell of each row (column D). Also, if possible I would like the formula to tell me how many times each keyword exists in each article text.

So for example, if I have 1,000 rows articles about real estate in the spreadsheet and I have a list of keywords like:

mortgage loan, kitchen, realtor, house appraiser, plumbing, electrical

If an article contains the word "mortgage loan" twice, "house appraiser" once, and "electrical" three times, then I would like the formula result to indicate which keywords it found in that article, and if possible how many times it found each keyword in that article. (To calcluate each row separately I could use the fill-down command to copy the formula in a column to calculate this for each row of the 1,000 articles).

If possible, I would like the formula to list the result from highest keyword frequency to the lowest. So in this example, the result would be listed as:

electrical 3, mortgage loan 2, house appraiser 1 (if the keyword frequency amount can be listed in the result)

or else list the result as:
electrical, mortgage loan, house appraiser

Thank you very much for any advice you have!

Hi All,

I have a following requirement.
I want to search for three words in a string, if any of the keywords is found first in the string, then that keyword should be placed in the cell left of the cell in which string is there.

For example:
Cell B1 contains "System PR1 is connected to PC1 with an interface at PB1."
No I want to search for any of the following keywords:
PR1 or PC1 or PB1.
Since it will find PR1 first in the string, so in cell A1, it should return PR1, and should omit searching remaining keywords, since it has found the first keyword that I am looking for.

Let us suppose if PR1 is not in the string then it should return PC1 in the cell A1 since it is a second keyword I am looking for in the string if available, if PC1 is also not in the string, then it should search for PB1 in the string and if it finds PB1 in the string then it should return PB1 in the cell A1.

If none of the keywords are part of the string, then it should return whatever I want to return like "Null" or "Not Found".

I tried the Find command but it is not working in the above scenario, and does not start finding for the second keyword if the first keyword is not found.

Any help would be of great help for omitting the manual work I am performing in Excel.


Thank You,
Saurabh.




I have a spreadsheet of data, I need to search the data for keywords and when I find a keyword return its adjacent value.

My Formulas and Keywords will go on sheet 1 and my data is on sheet Data. I am using Excel 2010

I need the following logic in a formula
Read Keywords from B1:B20
Find keywords on the data sheet A1:A100
Find a keyword
Return Adjacent value
If no keyword is found return blank

Sheet1
A1 - Where my formula will go
B1:B20 - Where my keywords are

Data
A1:A100 - List of words
B1:B100 - List of numbers

Hi All,

I have a following requirement.
I want to search for three words in a string, if any of the keywords is found first in the string, then that keyword should be placed in the cell left of the cell in which string is there.

For example:
Cell B1 contains "System PR1 is connected to PC1 with an interface at PB1."
No I want to search for any of the following keywords:
PR1 or PC1 or PB1.
Since it will find PR1 first in the string, so in cell A1, it should return PR1, and should omit searching remaining keywords, since it has found the first keyword that I am looking for.

Let us suppose if PR1 is not in the string then it should return PC1 in the cell A1 since it is a second keyword I am looking for in the string if available, if PC1 is also not in the string, then it should search for PB1 in the string and if it finds PB1 in the string then it should return PB1 in the cell A1.

If none of the keywords are part of the string, then it should return whatever I want to return like "Null" or "Not Found".

I tried the Find command but it is not working in the above scenario, and does not start finding for the second keyword if the first keyword is not found.

Any help would be of great help for omitting the manual work I am performing in Excel.


Thank You,
Saurabh.


Search keyword in a column & if any keyword match then cut that full row & paste it into another sheet.
--------------------------------------------------------------------------------

Hi,

please help me out for a macro solution for below routine process.

In one excel workbook, 10 worksheet is available name is given like (HP, ACER, Toshiba....) and in first worksheet name is (all_data) includes all models of HP ACER & Toshiba, i want to assign few keywords related to hp laptops, that keywords will be found in all_data worksheet, macro should automatically match any keyword related to hp & move that row to HP worksheet, same i would like to assign few keywords for acer & toshiba laptops, macro should automatically match keywords assign to acer & toshiba & move that row to acer & toshiba worksheet.

Please help me out with the macro,

thank you in advance.

I have a report with 3,000+ rows. Each row, the first column is a word. What I am trying to do is setup either another column or separate worksheet with a set of 50 words which is the search terms. Is there a command or macro that will somehow filter the large list to only show terms that match exactly the smaller list of terms.

I have attached an example with two worksheets, I want to filter the large list to only show exact match results for the keywords in the small list.


Hi,

I use the following, currently, to mark a row for exclusion

=IF(AND(ISERROR(SEARCH("GENERIC",A2,1)),ISERROR(SEARCH("PSOFT",A2,1)),ISERROR(SEARCH("dnd",A2,1)),IS ERROR(SEARCH("temp",A2,1)),ISERROR(SEARCH("train",A2,1)),ISERROR(SEARCH("do
not delete",A2,1))),"","EXCLUDE")

I'm looking for a way to use an exclusion keyword list on a separate
sheet in a dynamic range
so users may add to or delete exclusion keywords
rather than have the 'hardcoded' exclusion keywords in the formula
above

And I'm hoping to have the resulting value be the keyword found rather
than "EXCLUDE"

I've fiddled around with index/match (something I haven't quite
mastered)
and I have not found a way to make it work correctly(maybe because I am
searching for key words contained in cells that have sentences rather
han exact matches?)

This isn't working
=INDEX(ExcludeList,MATCH(ExcludeList,A2,1),1)
(the Range is named ExcludeList)

I'm wondering if there is a way to use SEARCH somehow but that doesn't
seem to work with a named range?

Any help is appreciated.




I need help.

I need to find rows (row B) with a keyword in them, and then move that row, the row before the keyword, and the row AFTER the keyword to a specific sheet.

SO for example.


Row 1 - Text
Row 2 - Contains the keyword
Row 3 - Text

The macro finds the keyword and moves rows 1 thru 3 to a different sheet.

It would also be very helpful if the macro could search for an array of keywords.

ANy help would be greatly appreciated!


Since my last post was so quickly and expertly solved (thanks again all), thought I'd press my luck with a bigger roadblock.

Here's the scenario (sample workbook is attached):
I have a series of keywords (or phrases) listed in column A of sheet2.
I have a long list of financial indexes on sheet1: Col B contains the name, and Col C contains the description.

Here's what I'm trying to accomplish:
I need to list out every index which has at least ONE keyword in the name, and/or at least TWO keywords in the description.

I'm currently doing this in two phases...first is the name checker:
Code:

Sub finddesc()
Dim keyword As String


Sheets(2).Select
Range("A2").Select

Do Until ActiveCell = ""
keyword = ActiveCell
Application.StatusBar = keyword
Sheets(1).Select
Application.ScreenUpdating = False

Range("C1").Select

Do Until ActiveCell = ""
If InStr(ActiveCell, keyword) > 0 Then
    Range("xfd" & ActiveCell.Row).End(xlToLeft).Offset(0, 1) = "Found in Desc:  " & keyword
End If
ActiveCell.Offset(1, 0).Select
Loop

Sheets(2).Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub


Then I run the same on the descriptions, and do a few other things... but this is horribly inefficient and taking a VERY long time.

Any help/guidance is appreciated!


I have searched the forums and have found things that are close, but not exact. This is my first post, so forgive if my syntax is not correct.

I have a list of text strings (from our support log) that I want to categorize. I want to search the text and find keywords. Then return a value from a lookup list, based on the keyword it finds. Basically a vlookup with a twist.


I have attached a sample of what I am looking for.

Hi,

I may need just some nomenclature or switch

I have named a range of "find text" in a column.
I am searcing a "within text" column of cells, each with a text string.
I want to return match / no match for exact phrases, in exact order, NOT case sensitive.
I also want to continue getting a match for sincle words in the "find text" column (list).

Currently I get a match if a complete find text word is found, which is good since I don't want partial words or compound words in the text strings to match.

BUT if there are two or more words in my find text phrase,
I get a match even if one word is in the "within text" column.

For instance:

find text:
brown cow
yellow cow

within text:
the cow jumped over the moon
2) the brown cow is a funny cow
the cow is mellow, the one that is yellow
brown and yellow are the names of my cows
5) shitbrown paddies line the cowyard
my son's hair has a brown cowlick.

I ONLY want to "match" for string example #2,
AND I want "No Match" for all the others.

Currently all of these except #5 match.

Also I have two ranges searching right now such as

CowsILike
Brown Cow
Yellow Cow

CowsIDon'tLike
Red Cow
Blue Cow

and the formula I am using is this:

=IF(ISERROR(LOOKUP(9.99999999999999E+307,SEARCH(" "&CowsILike&" "," "&A4&" "),CowsILike)),IF(ISERROR(LOOKUP(9.99999999999999E+307,SEARCH(" "&CowsIDon'tLike&" "," "&A4&" "),CowsIDon'tLike)),"MAYBECowsI'llConsiderLiking","CowsIDOn'tLike"),"CowsILike")


Thanks,

Dan


I perform a =Find(word,range) for certain keywords in text strings. Is there an easy way to have excel highlight the words within the string so i can easily identify its location? The text string might contain more than one keyword.

Any thoughts?


Search keyword in a column & if any keyword match then cut that full row & paste it into another sheet.

Hi,

please help me out for a macro solution for below routine process.

In one excel workbook, 10 worksheet is available name is given like (HP, ACER, Toshiba....) and in first worksheet name is (all_data) includes all models of HP ACER & Toshiba, i want to assign few keywords related to hp laptops, that keywords will be found in all_data worksheet, macro should automatically match any keyword related to hp & move that row to HP worksheet, same i would like to assign few keywords for acer & toshiba laptops, macro should automatically match keywords assign to acer & toshiba & move that row to acer & toshiba worksheet.

Please help me out with the macro,

thank you in advance.




Hello - I'm working with a rather large spreadsheet and need to find project names (listed within a column) that contain certain words, then return a "Y" or "N" in a separate column. What formula can I use? To add a bit of complexity, there are more than one keyword that I'd like to use in the search. Is it possible to create a formula with all keywords included so that any project name containing any of the keywords will return a "Y"?

I added a very simple example of what I'm talking about - where I'd be searching for the word "bird" in column A, and returning a "Y" in column B.

Thank you!

I have a dataset containing 15,000 records which needs to be categorized into product categories depending upon keywords found in the records. I have a lookup table which contains keywords and corresponding product categories. Below is an example of the data:

Item Keyword LOOKUP Matl group Keyword Matl group Material Group Description 1 RESISTOR RESISTOR 500010020 Elec-Electronic RESISTOR 10 MOTOR 500010030 Elec-Motors/Drives RESISTOR 100 POWER CORD 500010050 Elec-Supplies RESISTOR 100K HEAT TREAT 500020040 MTP-Heat Treat RESISTOR 10K ADAPTOR 500020050 MTP-Machine Parts RESISTOR 1K PUMP 500030000 Major Equipment RESISTOR 4.7K ADHESIVE 500040010 MRO-Acids/Oils RESISTOR 49.9 NAILS 500040030 MRO-Building Supply

I am trying to lookup the Item Keyword value in the leftmost column and find a similar match in the Keyword column, and then return the appropriate product category/matl group. The issue I am running into is the Item Keywords sometimes contain more text than the Keyword and the lookup/matching will fail, as in the case of the RESISTOR keyword. Now if I perform a reverse lookup from the lookup table itself this will work, considering that RESISTOR will be found in all of the Item Keywords. Is there anyway I can use wildcards or another function so I perform a normal lookup?


I have a long list of keywords and I am interested if it is possible to write a formula that can identify cells that have a particular keyword withing a cell. For example, lets say that I am looking for all the keywords that have "1031" in them, so if the keyword is "1031 tax exchange" I would want to be able to ID this keyword.

Thanks


Hi All,

I am facing a problem in excel. Suppose I have entries in Column A and I have a few keywords in Column D. I need to check if the entry in column A contains one of the kepwords in Column D. The formula for the check will be written in Column B and if it contains the keyword, I need to display it in column B.

For Eg.

Cell A1 has an entry "ABC Holdings Limited" and the keywords in Column D are

Cell Keyword
D1 Company
D2 XyZ
D3 ABC
D4 Others

Now since A1 has "ABC" in it which is one of the keywords, I need to display "ABC" in cell B1.

Hope I am clear.

Cheers
Rishu


Here's what I've got....

I need a macro that searches my spreadsheet for a keyword in Column B. If it finds the keywords (or an array of keywords would be even better), it then deletes the entire row above the keyword, the row with the keyword, and the row below the keyword.

So for example,

(Column B)
1 - Row Above
2 - Keyword Row
3 - Row Below

If the macro finds the text in column 2, it would delete 1, 2 and 3.



I need to have a second macro that performs a very similar function -- it copies the 3 rows (similar to the example below) to a different sheet based on the keyword.


Any help on this would be greatly appreciated!!!!


In Excel I'm trying to find if any keyword from a list of keywords exists in a string from within an array.

I'm aware of the instr function, but does this work for searching a string from an array of keywords?

I'm not sure how many keywords I am likely to require, so ideally wanted a solution that didn't involve a loop.

Hope you can help!




hi, this is my first post ever,

I am needing help, I have 2 different formulas each does part of what I need it to do as a whole, I've attempted to nest the two formula's together without any success. I am hoping someone can help me with this.


This formula looks up a Keyword (full or partial) in a string of text and returns the 1st occurance then stops
IF(E7="","",VLOOKUP(E7&"*",'ACTION RESULT CODES'!B2:F16,1,FALSE))
&
This formula looks up the exact word in the cell and returns all occurance of that word (by dragging down the formula)
IFERROR(INDEX($A$5:$B$11,SMALL(IF($A$5:$A$11=$E$4,ROW($A$5:$A$11)-4),ROW(A1)),2),"")

I need the formula to do what the 1st one does look up keywords in a string of text and then return all occurance for that word (or the data in the next column). I am unable to use vba for this project.

Thanks!!



Hello All!

I'm trying to automate creating certain keyword combinations I need, based off of the values I input into reference cells in columns A - E; the goal is to compile a list of keywords which I will then use to track my rankings in search engines. But there are three things I simply do not have the smarts to figure out, and I was hoping someone in here who is a bit more seasoned with excel formulas could provide me with some guidance. Below is what I am trying to accomplish:

I'm looking to only output 500 keywords, so some of the cells in columns A, B, C & E will not contain data (column D will always have a primary Geo-target listed). This results in some of the concatenate formulas I've created outputting partial data (i.e. if there is no data in cell A10, and cell D2 contains the word "Knoxville", then cell I10 will output the data, "Knoxville "). How can I setup conditioning formatting or a formula so that these auto-generated cells appear blank if one of the reference cells has no data within it? The reason why I need the above to work is because I want to setup a formula that automatically counts the # of keyword combinations created by the data entered into any of the reference cells. With the partial combinations being listed, it skews my data. Which leads me to my next question: what is the best formula for counting the # of cells containing a full keyword combination from any of the cells listed in columns G - O (minus the data in the header cells; i.e. G1, H1, etc...)? Lastly, is there a formula I could use that would then aggregate all of the full keyword combinations within the "Complete Keyword List" column (column P)?
Hopefully the above explanations and requests are not too confusing and/or difficult. I've attached an example document (click he Ranking Reports - Automated Keyword Template.xlsx), which will hopefully increase my chances of getting some help on this one.

Thank you for your time and consideration, and please let me know if anything else is needed.



Hi All -

I have a range of cells in a row that have multiple words in each. I am trying to use a formula that will parse through the range of cells and identify key words and classify the range into a new category.

I have tried both =IF(COUNTIFS(E1:K1, "keyword", "keyword 2", "keyword 3"), "Classification", 0) and =IF(SUM(COUNTIF(E1:K1,{"keyword","keyword 2","keyword 3"})),"Classification", 0)

I was working off of this previous post http://www.excelforum.com/excel-gene...53#post3419053

I have a worksheet that has several data sets in one column in-between 2 Keywords.

Example:

Keyword
One
Two
Three
Four
Five
Keyword
Keyword
Six
Seven
Eight
Nine
Ten
Keyword

The pattern will always be Keyword, rows with data, Keyword, Keyword, more rows with data, Keyword . . .

I'm looking to copy all values between each set of Keywords, then paste them transposed in columns, then find the next set, paste in row below 1st, etc., till all the data has been transposed in it's proper row.

Example:
Keyword One Two Three Four Five Keyword
Keyword Six Seven Eight Nine Ten Keyword
etc. . . .

Below is my first draft, modified from code in this link (http://www.mrexcel.com/forum/showthread.php?t=351699) but I'm getting this error:

'copy and paste areas cannot overlap unless they're the same size and shape'

I also am not pasting correctly, it needs to go down 1 row each time.

Code:

Sub TransposeData()
Dim lastrow As Long, i As Long, iStart As Long
Dim rng As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow
    If Range("A" & i).Value Like "Keyword" Then
        iStart = i
    End If
    If iStart  0 And Range("A" & i).Value Like "Keyword" Then
            Set rng = Range("A" & iStart & ":A" & i - 1)
            Selection.Copy
            Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    End If
Next i
End Sub


Where am I going wrong? Thank you.


I am not that proficient in Excel, but I would like to know if there is a way to perform a function or program/macro that will allow me to create a robust list of text variables quickly.

For example:

In column A, I have "word 1, word 2, word 3, word 4, word 5" in cells A1-A5 respectively.

In Column B, I have "word a, word b, word c, word d, word e" in cells B1-B5 respectively.

I need the final list to combine "word 1" with "word a, word b, word c, word d, word e" for the final output of "word 1 word a, word 1 word b, word 1 word c, word 1 word d, word 1 word e" and so on down the list until all possible combinations of words are accounted for and present.

This is in an effort to quickly and efficiently create keyword combinations for an online PPC program that has over 470 campaigns, 2800 ad groups. So, manual creation of keyword variations is just not efficient or an option.

PLEASE HELP!!