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

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 ...
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 ...
Formula to Delete the First or Last Word from a Cell in Excel
Excel formula to delete the first or last word from a cell. You can copy and paste the formulas below for a quick f ...
Count the Number of Cells that Contain Specific Text in Excel
How to count the number of cells that contain specific text within a spreadsheet in Excel. I'll show you how to cou ...
Determine if Cells Contain a Specific Value in Excel
Find if a cell or range of cells contains a specific value in Excel. This method can be used on individual cells wh ...
Quickly Replace A Lot of Data in Excel
The Find & Replace works much the same way as Find and is located in the same place. (Find & Select drop do ...

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







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.


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


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


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, I would like to add a "+" sign before each word within a bunch of cells. How would I be able to do this with a formula? Should I split up the cell into its component words somehow, then add the +, then concatenate them together? Any help would be appreciated. Looking to do this for an Adwords account to turn keywords into modified broad match keywords.

Thanks much.

Jim


Wondering how I can go about this.

Have a long list of bond names (full text string I want) and another list that is compiled with essentially the same data, just more of it and unsorted. Is there a formula that is capable of matching the 3/4 text strings I have to the full text strings I have? the 3/4's are not "closest match" they should link up exactly to a full string in other cells.

For example, A1:A2800 = full text strings (columns b, c, d have other data) E1:E500 has the 3/4 text strings that I want to reconcile with the full ones. Want to lookup the full text string, find the 3/4 string tha matches and then have the corresponding data to the 3/4 strings pulled out.

let me know if you need more clarity.

Thanks in advance.

Rob.


Hi there,

I have a column filled with company names. I need to remove specific words from the cells in the column such as 'the', 'of', 'university' etc. Does anyone know if it is possible to do this using a formula in Excel 2003? I thought I could maybe record a macro and use find/replace but it was removing those strings of letters where they occured within a word too (e.g. 'the' was removed from the word 'southern' leaving the mangled word 'sourn').

A bit of context in case this helps... I have data which I want to match with this list of company names using a lookup function. At present I'm only getting a result where the cell contents match exactly. So, I want to remove as many extraneous words as possible to increase the possibility of getting a match.

Many thanks in advance for any help people can offer.

All the best

Doug


Track red car
best blue ball
red car track
blue ball best

Is there a formula/VBA that I can use to identify that a keyword has a duplicate word order under it. For example, identify that for 'track red car' there is another keyword in the list which is using hte same words but in a different word order, for example in row 3 and similarly show that the keyword 'best blue ball' has another keyword with different word order below it in row 4. My ultimate goal is to keep the first words and remove any duplicates that are present.

Please advise.

-Faku


I have a column of cells each containing a text string of varying lengths.

What I want to do concatenate the text together (found a nice UDF that does this by specifying a range, rather than use the standard concatenate forumla) and then I want to remove common words such as "the", "as", "it", etc. to try and ensure the volume of characters stay beneath the character limit for a single cell for Excel 2007, which I believe is slightly over 32000.

I could (and have so far), got a list of the top 500 words in the English language and used SUBSTITUTE to replace the common words. But this produces either a huge single cell forumla (with one SUBSTITUTE per word - but I eventually hit a limit on the length of the formula), OR, I do a SUBSTITUTE for each word I want to replace in it's own cell, creating a huge column or row of cells with a SUBSTITUTE formula.

As I am dealing with over 13000 cells with text in them, the worksheet quickly become very unhelpful in its size.

Is there any UDF (called, say, REMOVE_COMMON()) that could reference a cell of clutered text and then replace the common words by either looking up the words to be replaced from a separate column of words elsewhere in the workbook, or have the words to be replaced listed within the UDF itself, that one could then go in to the VBA (using Alt + F11) and add the extra words that need to be removed?

My end goal is to create a single cell of text that I can copy off and paste the raw text into www.wordle.net. I know Wordle ignores common text, by the problem is, there is SO much text in the 13000 rows of text data, that I need to find a way to reduce the volume of words BEFORE pasting into Wordle, as Wordle itself seems to have a limit to the number of words you can paste into it.

Hope this is clear. Any help would be much appreciated and save me a whole load of huge worksheets.

Kind regards


Padster


Hello Everyone!

I'm having trouble and was wondering if you could help.

Using Excel 2007.

I have 10 .xls Files, each with 4 FULL spreadsheets, with over 2GB of data.
Was wondering if there is way to copy certain rows that contain specific keywords.

For example: all rows that have the word "green" in them and all rows that have the word "blue" and so on.

I tries using search function (CTRL+F) and used "Find All" option. It did find all the occurrences. When I selected all the findings it limited selections to specific CELLs now the ROWs.

I was wondering if there is a way to select ROWs with specific KEYWORDS in them.

Thank you!


Hi,

I'm doing my thesis and I'm looking for a list of VBA keywords / reserved words. I know there's words like sub, end, function, select, for, each, if and so on and so on. But where's the complete reference to that list? I can easily find one for example for C, so I would imagine a similar list would exist for VBA too.


I have 2 sheets - I need to reference one to get detail on the second sheet that is on the first sheet. I was initially going to do a Vlookup with the names ( each sheet has names of people) but unfortunately it isnt that easy.
The first sheet has names on it such as, "Doe, John H."
The other list will have it listed as "John Doe"

Is there an easy way to do a find for the first word in this file ( the last name) and grab the cell with that in the 2nd list?

I hope this makes sense. I attached an example in case it didnt.
Thanks so much for any help at all!


Hi everyone. I have a very ambitious project that I am working on. There are many stages to it and it may well melt my CPU! However, I must first make a few tests and for these, some advice would be of great value, if you can help.

First of all, I need to be able to index all of the words in a large selection of txt files. Each unique word needs to be placed in a cell in a big long column. I expect there will be tens of thousands of words in all of the text files so I mean a REALLY long column. I also need to count the number of incidences of each word over all the txt files and have this "count" as a separate column.

The next step will be to remove words that are superfluous like "the, it, and ... etc" and words that are so rare that they do not provide any useful information. This, hopefully, will leave me with a list of common keywords from the txt files.

This will then need to be put into alphabetical order!

Presently, the txt files are in pdf form so I have a little bit of work ahead of me before I can begin. Any advice on how to index words in the way described above would be most valuable.

I am realising more and more, that a good bit of skill with macro-programming will go a long way if I am using Excel for stuff like this. Can anyone suggest a good book that might help?

Many thanks.

Adam


Hi. I can't figure this out even after searching this forum with keywords such as "font color change macro"

I have text in Column D. It consists of phrases such as:
JUL 2009 EXAM
FEB 2009 EXAM
JUL 2009 LIST
FEB 2009 LIST
JUL 2009 OUTLINE
FEB 2009 OUTLINE

I need a macro to change the font color of the text in Column B of each row based on the text in Column D. If the text in column D contains the word "EXAM", the text in Column B for that row should be Red and bold. If the text in column D contains the word "LIST", the text in Column B for that row should be Blue and bold. If the text in column D contains the word "OUTLINE", the text in Column B for that row should be Green and bold. I know I can do this with conditional formatting, but it must be a macro that actually changes the properties of the text in Column B since I use Excel spreadsheets to do batch search and replaces in Word documents with a program called Useful File Utilities and it does not recognize the conditional formatting. I only want to change the font color for text in Column B. The other columns should stay the same. Thanks.


Column "G" contains a list of names most of which are more than one word. The formula


Code:

=COUNTIF(Summary!$G:$G,($H9))


works if H9 contains an exact match. I am looking to be able to find all instances of a word in the list, ie. if the list contains cells with team red and another cell contains team green, I want to find all instances of red or green by entering the word red or green in cell "H9".


Thanks
Jim O


I know there are many posts concerning this, but after scouring, I couldn't find one that fit my situation. I have a total of six worksheets, I am only concerned with two worksheets.

Worksheet (functions!)
This one has a list of numbers formatted as general. (Column G)
Is actually a formula/macro that outputs a number... (didn't know if this mattered?)

Code:

=IF(ISERROR(SEARCH(".",F192)),CleanAll(F192),CleanAll(LEFT(F192,FIND(".",F192)-1)))


Worksheet (ACD!)
This one has a list of numbers formatted as text. (Column A)
Actual typed numbers.

I want to conditionally format the cells in (functions!$G) to turn green if it finds a match in (ACD!$A), and turn red if it doesn't find a match.

Thanks in advance for the help.




have created a sheet to paste in text and output lower, UPPER, and Proper text I have a named range of prepositions and articles capitalizerd.

I am trying to use the SUBSTITUTE formula to replace all the occurrences or words that appear in the prepositions list

so instead of

Our Breakfast Is Consistent

We get

Our Breakfast is Consistent

so far I have this formula, but it does not work

=SUBSTITUTE(D6, TRIM(prepositions), TRIM(LOWER(prepositions))

*ignore the TRIM, Later I added spaces around the named range words, so that BUT STOPPING did not become "But StoPPING to"

I want to use proper + substitute prepositions and articles to lower text.
The reason for all this is that we have to often flow in lots of text from a csv into Adobe InDesign. Tickets and Menus.
Sometimes a client will send TEXT IN ONE FORMAT and then In Camel Case in another for the same brand and release.
Consistency is key and Excel often helps us achieve this much quicker. I could do this in PHP pre_replace but I am trying to do this in Excel and pass round the office.

Seems to me I might need some kind of split word and Vlookup combination? Not necessarily and INDEX/MATCH.

Can anyone help. Would be very appreciated. Any advice would be gratefully received.text_replacement.xlsx

Thanks

Andi

Please see attached sheet

Is there a simple way to search within a large text string to see if at least one short text string from a list is present? I do not want to have to test for each of the short strings individually inside an OR() function.

For example, I want to search a fast food restaurant address in cell B1 to see if it contains any of the 10 string fragments: "Burger Kin", "acdonald's", ..., or "aco Bell" tabulated in cells C11:C20.

I've tried using array formulas like {=SEARCH(C11:C20,B1)} and {=MATCH("*"&C11:C20&"*",B1)}, but they only work for the first item in the list. I've also tried incorporating OR() functions in the array formulas, but they do not work with variables (references to locations in the table, rather than the strings themselves).


I'm trying to compare a very large amount of data with other data in a spreadsheet.

What I want to do is take a text string like and see if it matches anywhere else in the spreadsheet:
"Fireflies, Owl City"

I have tried using the exact function and a few others, but haven't been able to achieve the desired results.

What I would like is for the program to automatically state whether there is a match in the spreadsheet based on the text strings in another column or row.

For an example of what I mean, see the attached spreadsheet.

Please note that in this case this function will not work because it is not guaranteed to be in the same order.

Anyone have any suggestions?


I'm using Excel 2003. My macro takes a 40,000 line (approx) CSV file, and among other things finds rows with 10 keywords in the row and deletes the entire row.

The problem I want to solve is that the find loop is very very slow. It takes about 1-2 seconds per row to search through all my keywords. 40,000/60/60 = 11 hours.

The find operation is slower with the more words I add, but even with just one or two words, it was kind of slow. I have found that the find/replace command is much quicker, but I can't delete entire rows with that method and that is what I must do.

My approach uses a for each loop to iterate through each row. From there, I use a loop to find each of my key words in that row. Keywords are stored in an array.

Help to speed up this process would be appreciated!

Gabe


Hello

I am using MATCH to find a value in a list of data on another sheet.

However, there are multiple matches and the formula only returns the first value. Is there a way I can tell excel to return the latest value at the bottom of the list and not the top?

My formula at the moment is:

Code:

=IF(ROWS($2:2)<=COUNTIF('All Incidents'!$N$3:$N$1000,"Open"),IF(COUNTIF(Updates!A:A,A3),INDEX(Updates!D:D,MATCH(A3,Updates!A:A,0)),"0"),"")


Your assistance will be greatly appreciated !


I have two spreadsheets where I have a match on a partial text string with another text string in column A of each spreadsheet, where a match exists.

I need to lookup in the following 9 columns (B to J) in the same row as the match in each case a existing number and multiply it by the corresponding cell in the other spreadsheet where the match in column A occurs in each and every case.

Is there an if(vlookup( function that will deal with this where a match is found having said the text string is only partial?


Hi folks, I've got a scenario I need help with. I've got an Excel file containing names, last names in column C and first names in column D. I've got another file containing a different list of names in the same format. What I want to do is check if the full names in file 2 match any of the full names in file 1.

Here's an example:

File 1:
DOE | JOHN |
DOE | JANE |

File 2:
DOE | JOHN | MATCH
DOE | ANDY |

I'm looking for a formula that will see if the values in that row of column C and D in file 2 match any rows in column C and D of file 1. So if there's a JOHN DOE in file 1, the formula will return "MATCH" or some confirmation that the names are in both lists, and return blank if the full name isn't in file 1. I tried all kinds of combinations of VLOOKUP, EXACT, FIND, MATCH, AND, IF, and ISNA, but the best I've come up with is two separate lookups and a check if both matched. The problem with that is that if I'm looking for JOHN DOE and there's someone with the last name of DOE and the first name of JOHN, the check will return true even if the matches aren't in adjacent cels in the same row. Any help would be greatly appreciated. Thanks!


I have a set of over 18,000 keywords in a spreadsheet. I need to group the keywords into categories. Is there a way to grab all cells containing a specific word like "computer" and then copy/paste all those cells into a new column?


Hello,

I have a list of surnames in sheet 1, column B

I have another list of surnames in sheet 2. column B

What i want to achieve is for the list in sheet 2, to be compared to the list in sheet 1, and return either a YES or a NO (TRUE or FALSE would work fine), in sheet 2, column C - next to the corresponding name


so i want to ask excel to look up the name in Sheet2!B1, in my column Sheet1!B:B

and if it finds a match, return "YES" in Sheet2!C1, if it does not find a match, return "NO" in Sheet2!C1

i hope that makes sense, i have been playing around with the IF, VLOOKUP, and MATCH functions all morning but i cannot seem to get it right.

Thanks.