Filter column by exact match?

**mikey42979** · 06-20-2013, 10:31 AM

How can i filter a column to show me all the cells containing the word "on" when i try to filter i still get results like canyon or ontario etc which i dont need.

**Pete_UK** · 06-20-2013, 10:53 AM

Use custom filter | contains, and then put a space either side of "on".

Hope this helps.

Pete

**XOR LX** · 06-20-2013, 10:57 AM

Hi,

Hit the filter, scroll down to Text Filters and select the relevant option.

Regards

**mikey42979** · 06-20-2013, 10:57 AM

Use custom filter | contains, and then put a space either side of "on".

Tried that it didnt work.

**mikey42979** · 06-20-2013, 11:03 AM

Originally Posted by XOR LX

Hi,

Hit the filter, scroll down to Text Filters and select the relevant option.

Regards

This also does not work, contains does what i said above, equals doesnt work also and neither does begins or ends with. I think excels filter is almost useless lol.

**Pete_UK** · 06-20-2013, 11:07 AM

You could use a helper column then, with a formula like this:

=IF(ISNUMBER(SEARCH(" on ",D2)),"yes","no")

assuming your data is in column D, then copy down and apply filter to this column, looking for yes.

Hope this helps.

Pete

**MarvinP** · 06-20-2013, 11:15 AM

Hi Mikey,

Use an Advanced Filter like shown in http://www.contextures.com/xladvfilter01.html#critast

Make the criteria range this ( ="= * on *" ) with spaces before and after the "on".

See the example where I've done your advanced filter ..