How is searching by words different from a simple text search?
Mainly because when searching for short words using regular text matching, you might end up finding them inside other words. This means your filter results can include cells you don’t actually need.
Searching by words ensures that only cells where the words match exactly are shown.
Find a single word in Excel cells
Let’s start with the simple case—finding one word.
Filtering by a word in Excel
Excel’s filter tool offers three text filter methods—meaning you can filter by three matching patterns:
- Cell contains the word — add spaces before and after the word.
- Starts with the word — add a space after it.
- Ends with the word — add a space before it.
The problem is Excel can’t filter by all three criteria at once—you can only use two. Here’s a simple workaround:
- Make a copy of the original column.
- Remove all characters except letters, numbers, and spaces.
- Add a symbol to the start and end of each cell, for example “\”.
- Replace remaining spaces with the same symbol.
- Filter by your target word with “\” before and after it (e.g., “\word\”).
The “\” symbol ensures you’re filtering for whole words only.
As you can see, there are five steps—so if you only need to find one word once, it’s easier to just filter three times. But if you’ll be searching for multiple words, this will speed things up.
Example:
Another option is to add spaces at the start and end of the text. In this case, when searching or filtering, you must use the “*” wildcard before the left space and after the right space — otherwise Excel won’t recognize the spaces.
So, to search or filter by a word in Excel, add a space to the left and right of all cells in the column, then search or filter by the word including those spaces.
Searching for a word in a cell: formula
The ideal function for a formula-based word search is SEARCH.
Formula:
=ISNUMBER(SEARCH(" "&"yourWord"&" "," "&A1&" "))
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Text | Word | Fragment (“car”) | Whole word |
| 2 | car rental | car | TRUE=ISNUMBER(SEARCH($B$2,A2)) | TRUE=ISNUMBER(SEARCH(" "&$B$2&" "," "&A2&" ")) |
| 3 | scarf for sale | TRUE=ISNUMBER(SEARCH($B$2,A3)) | FALSE=ISNUMBER(SEARCH(" "&$B$2&" "," "&A3&" ")) | |
| 4 | Car wash | TRUE=ISNUMBER(SEARCH($B$2,A4)) | TRUE=ISNUMBER(SEARCH(" "&$B$2&" "," "&A4&" ")) | |
| 5 | cars and bikes | TRUE=ISNUMBER(SEARCH($B$2,A5)) | FALSE=ISNUMBER(SEARCH(" "&$B$2&" "," "&A5&" ")) | |
| 6 | buy a car, cheap | TRUE=ISNUMBER(SEARCH($B$2,A6)) | FALSE=ISNUMBER(SEARCH(" "&$B$2&" "," "&A6&" ")) |
A plain SEARCH finds “car” inside “scarf” and “cars”. Spaces around both the word and the cell text limit the match to whole words; the search ignores case. “car,” isn’t matched because of the comma — remove punctuation first.
Here yourWord is the word you’re searching for, and A1 is the cell where you’re looking for it. SEARCH returns an error, not FALSE, when the word isn’t found, so the check is wrapped in ISNUMBER: a comparison like SEARCH(…)>0 would show #VALUE! instead of FALSE.
Remember to remove punctuation first.
Searching by word in Excel using !SEMTools
The fastest method — available to full-version users of my Excel add-in. The process is simple: select the range, run the macro, type the word, and click OK.
Find multiple words in Excel cells
How can you check if each cell in a large range contains at least one word from a list—and make sure you’re finding whole words, not just text fragments inside other words? And what if you need to search hundreds of words across tens of thousands of cells?
Find any word from a list
!SEMTools handles this easily. Regardless of the list size, detection is very fast—even in ranges of 10,000+ cells.
To find a list of words in a range with !SEMTools:
- Copy the range to another column to preserve original data.
- Run the macro from the add-in’s panel.
- Select the list of words to search for.
- Click OK.
The macro checks whether a cell contains at least one word from the list.
Practical use cases
This is often used before two other actions—extracting words from a list and deleting a list of words from text. Why not do these right away? Because extraction is slower, and deletion won’t show which cells were affected.
Examples: PPC specialists might search for purchase or rental markers, reviews, or check long negative keyword lists to exclude certain queries from campaigns. Or, if you want to know whether a price list contains items from certain manufacturers (whose list you already have), this macro will help.
Find all words from a list
This search works the same way, except it returns TRUE only if all the words in the list are present in the cell. Quite a rare task though.
Why a word isn’t found: common mistakes
- Punctuation glued to the word. In “price, delivery” the word is followed by a comma, not a space, so the formula with spaces won’t see it. Remove punctuation first.
- Case sensitivity. SEARCH ignores case, while FIND doesn’t: with FIND, “Price” and “price” are different words.
- Word forms. Searching for “apple” won’t find “apples” — that’s exactly the difference between a whole-word search and a search by pattern.
- Non-breaking spaces. Text copied from websites often contains them instead of regular spaces, and the formula treats them as part of the word. Replace CHAR(160) with a regular space before searching.
See also:
Need to search in Excel by words?
!SEMTools can do it in just a couple of clicks!