Email:      Pass:    Pass?


Advertisements


Free Excel Forum

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!


Similar Excel Video Tutorials

Helpful Excel Macros

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
Vlookup Function That Searches The Entire Workbook - UDF
- This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
Send Emails through Outlook using Email Addresses from Excel and text from Word
- This macro allows you to send an email to a list of recipients through excel. The email will be sent through Outlook an
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Capitalize the First Letter of Every Word in a Cell
- This macro will make the text of any selected cell in excel proper case. This means that the first letter of any word in

Similar Topics







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!

I have a series of lines, each one identified by a keyword (or phrase) that is the first part of the string on that line. The keywords are not necessarily of the same length though.

I'd like to hold the keywords in a list and identify each line according to which keyword the leftmost characters of the line match. Ideally, something like the MATCH function would do this, but it only matches strings of a fixed length.

For example, let's say I have a line like:

TEXT created by Blah-de-blah on 27/05/2008 containing blah-de-blah-de-blah

My list of keywords might be:
ACCESS
BASIC
CSV
DATABASE
JAVASCRIPT
PROJECT
RICH TEXT
SPREADSHEET
TEXT
WORD

At the moment I am able to do a MATCH using the first three characters of the line with the first three characters of each list element. That works, but I'd like to build something more reliable and arbitrary for the day when list elements cannot be uniquely identified by the first three characters.

Does anyone have any recommendations?

Thanks in advance.


Hi guys,

I have column A containing approximately 300 keywords, some of these keywords have sales results associated with them. These sales results are listed in two other columns - Column B has a list of the keywords with results, and column C contains the actual results. Column B & C match up, but Column A does not.

Basically I want to do a search to find the keywords that appear in both column A and Column B, whilst making sure the results in Column C are also included.

So it should be that after the search it will match up like so:

COLUMN A COLUMN B COLUMN C
keyword 1 Keyword 1 Keyword 1 result


There are more keywords in column A than there are keywords with results in column B and C, hence why I need the search to help me match the results with the keywords

does anyone know how to do this?

Cheers


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'm trying to find a method for advanced filtering using multiple criteria.

Sheet 1 Column A contains the names of seminars. Column B contains keywords for those seminars. For example, in cell A2 we have a seminar for Personal Financial Independence. The key words for this seminar are listed in cell B2 as "Finance, Budget, Personal".

This is a huge list with multiple seminars. Some of the seminars have 1 keyword, but others (as is the case listed above) have multiple keywords each separated by a comma.

Sheet 2 contains only a list of keywords, each of which are listed separately in their own cell. So for the example above where "Finance, Budget, Personal" is listed in a single cell on Sheet 1, each of these words are listed separately on Sheet 2.

What I'm trying to do is create a list box where the source is Sheet 2 (list of individual keywords). Based on the selections, the list of seminars on Sheet 1 is filtered. The problem that I can't get past is the fact that there are multiple keywords in a single cell on sheet 1.

For example, if someone wants to search our list of seminars based on the keyword "Finance", I need a filter that will search for the word "Finance" in the keywords column on Sheet 1. That means, that if Finance is only 1 word in a cell that contains 3 or 4 different kewords (separated by commas), I need this seminar to be listed. Likewise, I would like the ability to choose multiple keywords from a drop down list or list box. So, if someone wants to search "Finance", "Legal" and "Senior Citizens", all of our seminars would be filtered based on these keywords being found in the keyword column on Sheet 1.

I'm not sure if this is possible, but it would be incredibly convenient for us if we could establish this process.

Any help/direction you can provide would be extremely helpful.

Thank you.


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.

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


I have a Google Adwords campaign with around 200 ad groups. I would like to add some keywords which follow a similar structure to each ad group. They will be like this:

*standard keyword text 1* + location 1
*standard keyword text 2* + location 1
*standard keyword text 3* + location 1
*standard keyword text 1* + location 2
*standard keyword text 2* + location 2
*standard keyword text 3* + location 2
*standard keyword text 1* + location 3
*standard keyword text 2* + location 3
*standard keyword text 3* + location 3
etc

If I would like to add more keywords after I have initially done this, in the future I will probably want to add more keywords to each location so it may go like this:

*standard keyword text 1* + location 1
*standard keyword text 2* + location 1
*standard keyword text 3* + location 1
*standard keyword text 4* + location 1
*standard keyword text 1* + location 2
*standard keyword text 2* + location 2
*standard keyword text 3* + location 2
*standard keyword text 4* + location 2
*standard keyword text 1* + location 3
*standard keyword text 2* + location 3
*standard keyword text 3* + location 3
*standard keyword text 4* + location 3

Does anyone know how I can construct a spreadsheet to help me do this quickly and easily? Is it even possible?


Hello All.

I would like to compare some keyword lists if possible.

I have a large list of Unique keywords in Col A (From A3),
This Column is called Keyword List A - Large"

I then have a keyword list in Column C (From Cell C3),,
This Column is called "Keyword List B - Small.

I then have a column called "Unique Keywords Found",, This is Col E,, with hopefully returned results being entered from cell E3 downwards.

What I would like to be able to do if possible is run a Macro that would compare all the unique words in ColA and C and return only the difference, (The Unique words not found in ColC as Col A is the "Master List")

If possible could a pop up box appear saying
===================
List A No Rows:xyz
List B No Rows: xyz
No of Uniques Found : xyz
Time Elapsed (sec): xyz
===================

As a note I'm running windows XP and Excel 2007.

I hope someone can help me on this as it would be most helpful.
Many Thanks
John Caines


Hi,

I am attempting to assign industry and focus specializations to individuals based upon keywords found in their job title.

I have a column of keywords with corresponding values where the search begins, but I want excel to use information contained on another worksheet if it is unable to match a keyword to a word in the job title.

Example (see attached workbook):
Would like to search column D for keywords like "Highway" or "Planning" or "Operations" and have column E andF automatically update with corresponding industry and focus information. If that information is not available in Column D, I would like to have Excel vlookup using Column C (employer) and find the corresponding information in the lookup array in the worksheet called 'Toggles' in column D (1o Industry).
The function I created seems to work somewhat, but I don't want '0' entries if excel doesn't find the info it is looking for. Rather, I would like it to report 'Not Defined'. Other ways of doing this would also be appreciated.

Thanks.


Hi, I am fairly new to excel programming. I have a problem where i am currently stuck.

Say I have 5000 X 20 rows & columns of data. I need to filter them based on 3 or 4 search words and display the only resultant rows in another sheet.

Cell need not match the keyword exactly. For example the cell contains "blue greenyellow" and my keyword is "green", macro should still pick that entire row. Its like clicking ctrl F.

Ideal way would be

1. when I run the macro, a pop up opens and asks me "how many keywords?"
2. Once, I type say 3, 3 popups will promt me to enter 3 keywords.
3. when done, a new excel sheet open with all the rows (and all columns in those rows) containing those 3 keywords.

Also, it would be great, if excel can look entire workbook so that we do not have to specify the range in cells to look for in the macro.

Please help! Thank you.


I am currently tracking online PPC keyword reports with Excel and need to know if there is a function that will find and match words and phrases and then add the columns that are queried for the matching words/phrases. I think an example is definately in order.

December PPC
Keyword Clicks Cost Revenue
large dogs 45 .18 $12.00
small dogs 35 .25 $15.00

January PPC
Keyword Clicks Cost Revenue
large dogs 12 .14 $8.25
small dogs 18 .18 $5.35

Now using the example above I need a function or maybe even a macro that will scan all "keywords" and find a match for each keyword each month, say large dogs, and then add the clicks, costs, and revenue columns and post them on a particular row or rows.

So when running the function it will find and match "large dogs" for each month, it will then add the clicks for all months with "large dogs" in it and then populate a column or row with the total along with the keyword "large dogs" next to it. Is there a function that will do this or maybe even a macro?


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!


Please have a look at the attached sample sheet. The sheet named "category" has keywords under columns "Keyword1", "Keyword2", "Keyword3" & "Keyword4".

The sheet "Main" contains a column "Description" which will be searched against using these keywords..

The macro/function that I am looking for should search for all keywords in
"Keyword1", "Keyword2", "Keyword3" & "Keyword4".and if all of them exist in 1 line, it should stamp "Sub Category 1", "Sub Category 2", "Sub Category 3" & "Sub Category 3" besides "Description" column under the "Main" sheet.

I found a macro that works for a single keyword by looking only under "keyword1" but I need to get it working for multiple keywords.

For e.g "he" and "boy" are 2 keywords. If both are present on the same line under any of the "Description" rows, it should stamp the category "Gender" "Male"..

The current macro will stamp "Gender" "Male" only if "keyword"1 is "he is a boy" which means it is looking at the entire cell or phrase.

I have attached the macro that was found on the internet and a sample file with a preview of what I am looking for.

Thanking you in advance.


I got 2 worksheet.
Sheet 1: Columns of passages (each passage may contain none , or more than 1 keyword in the list in Sheet 2)
Sheet 2 : SIngle column of keywords

I want to see each passage contains each of the keyword in the list, then print the matched keyword in a row next to each passage.

I modify some VBA codes, but each matched keyword will only overwrite itself, by the first match??

Any expert can help??

Code:

Public Sub Test()
  Dim rng1 As Excel.Range
  Dim rng2 As Excel.Range
  Dim x As Integer
  Dim v1, v2, v3
  Dim i As Long, j As Long
  Dim Lposition As Integer 'to ensure next search will start after the first match sring
  
  Set rng1 = Application.ActiveWorkbook.Worksheets("Sheet5").Range("A2:A300") 'the columns of passage
  Set rng2 = Application.ActiveWorkbook.Worksheets("Sheet2").Range("A2:A180") 'the keyword list
  
  v1 = rng1.Value
  v2 = rng2.Value
  
  ReDim v3(LBound(v1) To UBound(v1), 1 To 20)
  
  For i = LBound(v1) To UBound(v1)
  Lposition = 1
  x = 1
  
    For j = LBound(v2) To UBound(v2)
    
      
      If VBA.InStr(1 + Lposition, v1(i, 1), v2(j, 1)) Then
        x = x + 1   'increase position of display column x
        v3(i, 1) = v2(j, 1)   'store each match in an array
        Lposition = InStr(v1(i, 1), v2(j, 1))      'Start next search after the previous string
        rng1.Offset(0, x).Value = v3         'print the match right to previous match, on same row i
        'Exit For
      End If
      
      
    Next j
  Next i
  
  
End Sub





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?


I have a workbook called Keyword that has two columns c,and d, of words.
The length of the columns can vary,because I add words to them.
I also have a workbook called testfile, and cell B1 has a word in it.
Testfile also has an .xla module that adds in when testfile is open.

I want to type a function called asset in cell C1 of Testfile,and have
it compare each word in Keyword workbook to the word that's in cell B1
of testfile. If a match is found in column C of Keyword, the function
returns the letter C. If match is found in col D of Keyword, it returns
the letter D. If no match, function returns "no match".

I have both workbooks open and the vba code I stuck at the bottom of
the .xla module as another function.

I'm very new to vba so speak slowly!
Here's my code so far.


Function asset(Description As Variant) As String 'arg. is the word in cell B1 in testfile


Dim LastRowOfC As Integer, LastRowOfD As Integer
Dim i As Integer

Description = UCase(Description)
Workbooks("Keyword").Activate
LastRowOfC = Range("C65536").End(xlUp).Row 'number of rows in these columns
LastRowOfD = Range("D65536").End(xlUp).Row

If Description = "" Then 'checks for cell with no description
asset = "no asset description"
Else
i = 1
Do Until (i = LastRowOfC Or InStr(Description, Cells(i,"C").Value) > 0)
i = i + 1 'Description is the word in cell B1 of Testfile workbook,
'and Cells(i,"C") is supposed to look at the keywords in
' the Keyword workbook.
Loop

If InStr(Description, Cells(i, "C").Value) > 0 Then 'If there's a word match
Select Case Cells(i, "C")
Case Is = ("VEHICLE" Or "TRUCK")
Asset = "Exempt"
Case Else: Asset = "C"
End Select
Else
i = 1
Do Until (i = LastRowOfD Or InStr(Description, Cells(i, "D").Value) > 0)
i = i + 1
Loop
If InStr(Description, Cells(i, "D").Value) > 0 Then
Asset = "D"
Else
Asset = "?"

End If
End If
End If

End Function


Hi all,

I have a database in which each row contains data on a particular event. Column F to I have keywords relating to that event. For examply, F2 will have one keyword, G2 will have one and so on till I2. Some rows will not have all the keywords, maybe only two or three so some cells will be empty. Although the events are unique, the keywords will repeat themselves in more than one row. So one keyword can apply to many events. What I want to do is, in a user form, generate a single drop down list based on all the entries in column F to I and when the user selects a keyword, on a separate sheet display all the entries relating to that keyword. The database is in sheet called "Log Sheet", The search result has to be displayed on "Follow up".
Thanks.

Bookabc.xlsm


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.


Hi Guys,

I wondered if anyone could possibly help me?

I am trying to do a SUM on values that have the same keyword. For example, I have a list of keywords in a column, and in the next column the numbers of searches for this keyword. Some of the keywords are duplicates so i want to add the numbers together.

so for example;

test-keyword | 93
test-keyword | 12


I want to be able to add 93+12 together because the keyword "test-keyword" are duplicates.

Is there any way to do this?

Thanks

Mike



----


UPDATE: Solved this myself, I needed to use the "consolidate" button in excel 2007.