How to you filter an Excel doc so that it only brings back rows that contain a certain word?

Question

I have a huge excel document, and I want to limit the view, so that I only see rows that contain a certain word ("ABCD") somewhere within these row (potentially at any spot in the row, like the beginning, the ending, etc).
How can I restrict the view so that only the rows with "ABCD" are displayed and the other rows all all hidden?
Thanks.

Answer 1

I have to agree with Russ and disagree with Frank and here's why:



If you do it the way Frank says and you set the filter for all of the columns to Contains the text ABCD. What will happen is that most likely all of the rows will get filtered out. The reason this would happen is because EVERY column would have to contain that word for it to show up if you did that. If you set the filter to cells that contain ABCD in Column A, that would filter the list down to just rows in Column A that contained ABCD. If you did the same thing, in Column B it would narrow that list further and show only the rows that contained that value in both columns A and B.



By doing it Russ's way, it would most like select only the rows that you wanted. I only see two potential problems in Russ's answer.



You might need to add a space between each entry for that column he was talking about and Excel can only take up to about 255 characters in a cell.



So you'd need a formula like:



=A1 & " " & B1 & " " & C1...



Here's maybe a better suggestion:



Press Ctrl + F



This enables the Find feature in Excel. Type in the word that you want to find then click the Find All button. Write down the rows that the word is found in by scrolling through the list at the bottom of the Find and Replace window. Then hide every row but those rows.

Answer 2

Somewhere off to the right, put in a cell that concantenates all the cells in that row (=A1&B1&etc). Then, select the entire top row. Then, turn on the auto filter (Data-Filter-AutoFilter). Go to the column off to the right, and click on the little arrow. In the box that comes up, select contains "ABCD". Good luck.

Answer 3

Select a cell at the top of your worksheet. Go To Data>Filter>Autofilter. All cells with a value in the top row will now contain a drop down arrow. Click on the dropdown arrow that you think contains the value and scroll down to it. Select that value. Only rows with that value in that column/cell will be displayed. You can drill down with the other arrows at the top as needed.

Answer 4

You've answered our question - you filter. Highlight the whole table, including column headings, up to Data, filter, Autofilter. The column headings will then have black arrows to the side. Click on the one alongside the column to be filtered, this will display list of all contents, then you filter accordingly. If this doesnt work, click on Advanced filter.

Another way would be to use Data, Sort, or better still, set up custom views.

Answer 5

sophisticated point. do a search over search engines like google. that will can help!