Match whole word in autofilter

[wisekat]

Is there a way to use the built-in Excel autofilter functionality to filter by exact words (the "whole word" option available in many text find dialogs)? For instance, I need to show only rows containing various combinations of the words "car" and "replace', but not "car" and "replacement". How to do that in Excel?

It would be nice if we could avoid creating special new columns or VBA coding.

 

[Fazza]

when filtering choose: equals car Or equals replace
OK?

 

[wisekat]

The column I want to filter contains various mixes of the words "car" and "replace" together with other words. Some examples: "used car replacement", "replace old car", etc.

 

[Fazza]

OK, I misunderstood initially.
I can't help on this, sorry.

 

[Fazza]

I woke up this morning with the thought of this question on my mind! This is simple I thought, add an extra column with a formula like =" "&A2&" "
Then filter on this new field for equals " car " or " replace ". Each with leading & trailing spaces. However it didn't work. The leading or trailing spaces are ignored when Excel does the filtering. So Excel doesn't apply the filter strictly. The spaces have some special treatment...

Then I changed to another character. I chose $. And this seems to work. So a formula in a new field ="$"&substitute(a2," ","$")
Now filter for $car$ or $replace$

if the $ isn't best for you, change to another character (other than space)

OK?

PS. I re-read your question & see you wanted to avoid adding special columns. So, I guess that means my suggestion is not the answer you're looking for: and maybe is already known. So, I'm still not much help on this. cheers

 

[wisekat]

Fazza, thank you for your idea. It works like a charm. I don't mind creating a special column for filtering like mine, though this way looks less natural.

The only correction to your answer from my side is the following. The formula template for the top cell in this special column should be

="$" & SUBSTITUTE(A2;" ";"$") & "$"

 

[Fazza]

good to hear. cheers