How to filter comma separated data in Excel?
In many Excel worksheets, you may encounter situations where cell contents consist of multiple items separated by commas. For example, a single cell might contain "Tom, Jerry, Sam", representing a list of names. When using the Auto Filter utility in Excel, it is only possible to filter by the entire cell value, not by individual items within the cell, as illustrated in the screenshot below. This limitation can be challenging if you need to find or work with rows that include a specific value within the comma separated data. In this article, you will learn several practical methods to filter and extract rows based on a single value from cells with comma separated lists, along with guidance for troubleshooting and tips for choosing the method most suitable for your needs.
Filter comma separated data in Excel with Custom Filter
Filter comma separated data in Excel with Kutools for Excel
Filter comma separated data with Excel Formula (Helper Column)
Filter comma separated data using VBA Macro
Filter comma separated data in Excel with Custom Filter
The Auto Filter’s Custom Filter feature provides a quick way to display only rows containing a particular value within cells with comma separated data. For instance, if you want to filter rows where column B includes the name "Tom", you can follow these steps:
1. Select the column containing the comma separated data that you want to filter.
2. Go to the Data tab and click Filter. An arrow button will appear beside the header of your selected column. This activates the filter for your data:
3. Click the arrow button, then choose Text Filters—you will see several options. Select Custom Filter to open a filter criteria dialog:
4. In the Custom AutoFilter dialog box, select contains from the drop-down menu. Enter your desired search term (e.g., "Tom"). This setting searches for the entered value anywhere within the cell:
5. Click OK. All rows that include "Tom" in the comma separated data will be instantly displayed:
This approach works well for simple searches. Note that the filter looks for the presence of "Tom" within the entire cell content, so it may match values such as "Tommy" or "Tomson" inadvertently. Always confirm that your search term uniquely identifies the records you want. If you need an exact match with strict boundaries (for instance, only "Tom"), additional steps or tools may be required.
Filter comma separated data in Excel with Kutools for Excel
The Super Filter feature in Kutools for Excel provides an efficient solution for filtering complex data, including cells containing comma separated lists. It supports multiple criteria, case sensitivity, filter by text length, and more, making it suitable for advanced filtering scenarios.
After installing Kutools for Excel, here are the steps to apply a filter:
1. Click Kutools Plus > Super Filter from the ribbon. The Super Filter pane will appear on the right of your Excel window:
2. In the Super Filter pane, click the 
button to select the data range that you want to filter. Make sure your range covers all columns and rows you wish to analyze. Click the horizontal line to expand the criteria section, making it possible to set up detailed conditions:
3. In the criteria box, select the header for the specific column to filter. Then, from the second list, choose Text and from the third list pick Contain. After this, enter the value you want to locate (e.g., "Tom") in the last box:
4. Then click the Filter button. Kutools will instantly filter the rows, displaying only those cells whose comma separated values meet your condition:
Kutools' Super Filter offers a user-friendly interface for complex filtering. It is ideal if you regularly deal with large and complicated datasets or need to apply multiple simultaneous criteria. One tip: if you want more advanced pattern matching (such as filtering only exact values and ignoring partial matches), use the additional condition options.
Free Download Kutools for Excel Now!
Filter comma separated data with Excel Formula (Helper Column)
Using a helper column with Excel formulas is a practical way to flag rows that contain a particular value within comma separated data. This method works well if you want precise control over the matching criteria and need to apply further Excel filters efficiently.
Applicable for users who:
- Prefer exact or partial matching with formulas.
- Need to customize criteria for complex datasets.
- Are working with large tables and want rapid filtering using built-in Excel tools.
Disadvantages:
- Requires use of additional columns and some formula knowledge.
- May not handle variants (such as spaces before or after commas) flawlessly without formula adjustment.
1. Insert a blank column next to your data (for example, insert column C next to original data in column B).
2. In cell C2 (assuming your data starts from B2), enter the following formula to check if "Tom" exists as a separate value in the comma separated list:
=ISNUMBER(SEARCH(",Tom,",","&B2&","))This formula adds commas before and after the cell contents to ensure "Tom" is matched as a whole word rather than a partial match within another word (such as "Tommy").
3. Press Enter to confirm, then drag the fill handle down to copy the formula to other rows.
4. Now, apply Excel's Auto Filter to your helper column. Filter the TRUE values to display only the rows where "Tom" appears as a standalone item in the comma separated list.
Tips and Considerations:
- If your cell values use a different delimiter (such as semicolons), replace the comma (,) in the formula with your delimiter.
- Always check for extra spaces; use the
TRIMfunction in your formula if needed. - This solution is fully dynamic for future edits—updated cell values will automatically update the filter results after recalculating.
Filter comma separated data using VBA Macro
For automated batch processing, you can use a VBA macro to scan your worksheet and filter rows containing a specific value within comma separated data. This approach is effective for large datasets, frequent repetitive filtering, or where you need to integrate the filtering into a custom workflow.
Applicable for users who:
- Must automate filtering for many rows or multiple sheets.
- Prefer scriptable, repeatable solutions.
- Are comfortable working inside the VBA editor.
Disadvantages:
- Requires enabling macros and basic VBA knowledge.
- May need to be adjusted for different delimiters or criteria.
1. Open Excel, then click Developer Tools > Visual Basic. In the Microsoft Visual Basic for Applications window, click Insert > Module. Paste the following code into the module window:
Sub FilterCommaSeparatedData()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim criteria As String
Dim outputRow As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
criteria = Application.InputBox("Enter value to filter (exact match):", xTitleId, "Tom", Type:=2)
Set rng = Application.InputBox("Select column with comma separated data:", xTitleId, Selection.Address, Type:=8)
outputRow = 1
ws.Rows.Hidden = False
For Each cell In rng
If InStr(1, "," & cell.Value & ",", "," & criteria & ",", vbTextCompare) > 0 Then
cell.EntireRow.Hidden = False
Else
cell.EntireRow.Hidden = True
End If
Next cell
End Sub2. After entering the code, click the 
Run button. A dialog will prompt you to enter your desired search value and select the target column. After running, Excel will hide the rows not containing the specified value in the selected column.
If the macro does not perform as expected, ensure your cell values are consistently formatted and free of unexpected characters. To unhide all rows after filtering, simply select all rows, right-click, and choose "Unhide." If you need to adjust the code for alternative delimiters, replace the comma delimiter in the macro with your preferred character.
These methods provide flexible options when working with comma separated lists in Excel—whether you're filtering manually, using formulas, automating with VBA, or leveraging Kutools for Excel. Consider the dataset size, frequency of use, and the complexity of your matching requirements when choosing a solution. If you encounter unusual results, double-check for hidden spaces, line breaks, or non-standard characters that may affect matching, and experiment with formula or macro adjustments.
Related articles:
How to filter data by multiple colors in Excel?
How to filter exact text in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in