Fix Excel Advanced Filter Not Finding Text After Line Breaks
Question details
The user is attempting to filter data using the Advanced Filter tool but finds it misses matching values if they appear after a carriage return or line break within a cell.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering complex cell data containing multiple lines of text using the Advanced Filter criteria range.
- Observed behavior
- Unlike the standard filter, the Advanced Filter defaults to matching text exactly at the beginning of the cell, ignoring matches that appear after a line break.
Ensure your criteria range includes the exact column headers used in your data list, and that there are no blank rows separating your headers from the data.
Use Wildcard Characters in Your Criteria Range
Adding asterisks (*) around your search criteria forces the Advanced Filter to look for the specified text anywhere within the cell, successfully bypassing the line break limitation.
Because Advanced Filter criteria match text beginning with the specified string by default, carriage returns disrupt the exact match. By utilizing wildcard characters, you instruct the application to find the target text regardless of its position in the cell.
Find the cells in your spreadsheet designated as the criteria range for your Advanced Filter.
Instead of typing the exact value (e.g., Value2), type the text surrounded by asterisks (e.g., *Value2*). Press Enter to save the criteria.
Navigate to the Data tab on the ribbon and click on the 'Advanced' button located in the Sort & Filter group.
Verify your List range and Criteria range inputs in the dialog box, then click OK to filter your data. The text after the line breaks will now be successfully matched.
Filter Complex Data Easily with WPS Office Spreadsheets
WPS Spreadsheet provides powerful and highly compatible data filtering tools. You can effortlessly use advanced wildcards to accurately filter multi-line cells and manage your data without complicated workarounds.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the multi-line cell data.
- 2. Prepare wildcard criteria: In your criteria area, wrap your target search text in asterisks (for example, *YourText*).
- 3. Access the Advanced Filter: Go to the Data tab on the top menu and select 'Advanced Filter'.
- 4. Execute the filter: Highlight your List Range and Criteria Range, then click OK to instantly filter the dataset.
Frequently Asked Questions
Why do regular filters find text after line breaks but Advanced Filters do not?
Regular filters are designed to search for the substring anywhere within the cell by default. Advanced Filter criteria, however, strictly evaluate the beginning of the cell's content unless wildcards are explicitly used.
What wildcards can I use in an Advanced Filter?
You can use an asterisk (*) to represent any sequence of characters, a question mark (?) to represent any single character, and a tilde (~) followed by *, ?, or ~ to search for those specific characters literally.
Can I copy the filtered multi-line data to another worksheet?
Yes. When opening the Advanced Filter dialog box, select the 'Copy to another location' radio button. Then, specify your destination range in the 'Copy to' field before clicking OK.
