Excel - Exacting Text String or Phrase Query Search?

Question

This is more of Customer Feedback and has always bothered me when using Excel. 

Why is there no simple way to conduct an exacting search or query within a column for a text string?

For example if I want to return all cells that contain a mention of the word "eat," I don't want to return "grEATer" or "swEATy" or "grEAT" or "bEATs" and so forth. 

I simply want records mentioning "eat."

Most databases or tools with querying capabilities do so by following boolean operators, where quotations around a term or phrase means search only for that phrase or term. 

For example if I search in Google I love to eat it will return pages with mentions of any one or all of those words and not necessarily as a phrase. 

But if I query "I love to eat" I will only receive mentions of that exact phrase. 

Excel would be exceptionally more powerful, and USEFUL, if it allowed some way to do this. 

Because I desire to search on specific brand names that are sometimes two words or very simple two-letter, three-letter, four-letter words that often make up other words. 

Thanks!

Answer 1

Hello,

the Excel search function and Google search differ considerably, and so do the examples that you give.

Excel searches for a substring in a longer string. If you want to find only strings that are words in their own right, then it stands to reason that there will be a space either before or after the word (or both), so you can do two searches, first for " eat" (space, eat), then for "eat " (eat, space). That should find all occurrences of the single word "eat".

Google searches for words by default, so searching for "eat" will not return beat or cheater. When you enter several words into the search engine box, it will combine them with AND and will try to find pages that have all of these words. If you enclose the words in double quotes, the search engine will try to find the exact phrase. 

You can also use Excel to search for an exact phrase. Use wild cards before and after, like "*I like to eat*" and Excel will happily return the cells that contain "I like to eat apples" and "this is what I like to eat".

Answer 2

You may enable "Match Entire Cell Contents" checkbox (highlighted with yellow in my snap shot) to get desired results. Then it will find only cells which have Eat as a full record in cell instead of Greater, beat, cheat etc.

Answer 3

Thanks!

That is helpful.

But what if the cell content is not a single word, but an entire sentence or phrase?

For example: 

A1 - I will EAT the food. 

A2 - 5 is GREATER than 2. 

A3 - She was SWEATY after the gym. 

A4 - Kanye believes he is GREAT. 

A5 - The BEATS headphones aren't as good as Bose. 

A6 - Don't CHEAT on tests. 

A7 - I could't EAT all of the pizza. 

In this case I want only the cells that contain the exact word "eat".

Wouldn't match entire cell contents not work, since the the cell's content contain more than the word "eat," for cells A1 and A7. 

The data I'm working with, the records are multi-word, not a single word - sentences.

Answer 4

Did you read my post above? I explained an approach. 

"...you can do two searches, first for " eat" (space, eat), then for "eat " (eat, space). That should find all occurrences of the single word "eat"."