Have you ever tried to exactly filter in Excel, it means to filter exact text. Normally, you can finish this job by using the Advanced Filter feature, and in this article, I will talk about some good tricks for you to filter exact text in Excel.
Filter exact text in Excel with Advanced Filter feature
For example, I have the following data range, and now, I just want to filter the cells content is KTE-1. Please do as follows:
1. Create the criterion in the worksheet, and then apply the Advanced Filter utility, please enter the column header name that you want to filter and enter this formula: ="=KTE-1" (KTE-1 is the text that you want to filter exactly) below the herder cell, and press Enter key, see screenshot:
2. Then click Data > Advanced, see screenshot:
3. In the Advanced Filter dialog box, select Filter the list, in-place under the Action, and then specify the list range that you want to filter and the criteria range that you want to filter based on, see screenshot:
4. Then click OK button, the exact text that you want will be filtered as following screenshot shown:
Filter exact text in Excel with Custom Filter feature
In fact, the Auto Filter also can help you to achieve the result as you need.
1. Select the data range that you want to filter exact text.
2. Click Data > Filter to shown the arrow button.
3. And then click the arrow button at the lower right corner of the cell, and then select Text Filters > Custom Filter, see screenshot:
4. In the popped out Custom AutoFilter dialog box, choose equals from the drop down list, and enter the text criteria that you want to filter based on, see screenshot:
5. Then click OK button to filter the exact text as you need.
Filter exact text in Excel with Kutools for Excel
If you have Kutools for Excel, with its Super Filter utility, you can finish some complex filter, such as filter cells with multiple criteria, filter cells case sensitive, filter cells with text length, and so on.
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.
Dear Sir, I am using Advanced filter and everything is working well.... However what I can't figure out is how to make the advanced filter I have setup search for a certain word or letters within text.
For Example: I type the word (butter) in the Criteria range, and I want to search for any transaction that conatins this word, However currently it returns no value, Unless I type the exact search as it is listed in the data list precisely...
How Can I overcome this Problem and be able to enter a word or any letters and return results that contain any of this using Advanced filter. Note: I know how to do this using Regular filter and drop down, but I want to be able to do it with advanced filter. Please anyone can tell me simply how to do it?