How do I filter a table based on text in multiple columns

[tbucki1]

Hello,

I am trying to filter a large table based on a specific text. For example my table has 100 rows and 15 columns all with different data. I would like to filter for only rows that contain a specific text. **Note this "text" might be in different columns. For example I might have the text "Car" in B1 and the text "Car" in D5...After running the filter for the text "car", these would be the only two rows left, the other 98 rows that do not contain this text will be deleted.

How do I tell excel to search by row for "text" and return only the rows that contain this text, regardless of what column it is in?

Also I would like to do this without VBA....if possible.

Thanks!

 

[Marcelo Branco]

You can use Advanced Filter with a formula as criteria.

See if this example with a small data sample (5 columns) helps

	A​	B​	C​	D​	E​	F​	G​
1​	Header1​	Header2​	Header3​	Header4​	Header5​		Formula​
2​	text11​	text21​	text31​	text41​	text51​		FALSE​
3​	text12​	text22​	text32​	text42​	text52​
4​	text13​	text23​	car​	text43​	text53​
5​	car​	text24​	text34​	text44​	text54​
6​	text15​	text25​	text35​	text45​	text55​

<tbody>
</tbody>

Formula in G2
=COUNTIF($A2:$E2,"Car")>0

Select the data range (A1:E6 in the example) and go to Data > Advanced Filter

Set G1:G2 as Criteria Range

Apply the filter and you get

	A​	B​	C​	D​	E​
1​	Header1​	Header2​	Header3​	Header4​	Header5​
4​	text13​	text23​	car​	text43​	text53​
5​	car​	text24​	text34​	text44​	text54​

<tbody>
</tbody>

Hope this helps

M.

 

[tbucki1]

Thanks for the input, but many of my cells would have the text car in it, with other text as well. For example "1995 Car" in A5, and "2006 Car" in C4. Using the formula you provided it returns the value "False" on my table...It would need to search the cells and if the cell "contains" the text "cars"...then just filter for those. How would you write this formula?

Thanks!

 

[Marcelo Branco]

Try this formula

=COUNTIF($A2:$E2,"*Car*")>0

M.