Others

How to Filter Excel Table Values That Start with a Leading Space

Natalie TaylorNatalie Taylor Nov 2, 2026 868 views

Question details

The user needs to filter an Excel table to display rows matching an exact text pattern that contains a leading space, such as " 99r".

How to Filter Excel Table Values That Start with a Leading Space
Product
Excel
Device & OS
not provided
Scenario
Filtering a dataset for specific strings where spaces are critical for differentiating between values.
Observed behavior
When entering a leading space in the standard filter box, Excel ignores the space and displays all cells containing the string, such as "299r" and "499r", instead of strictly matching " 99r".
Before you start

Ensure your dataset has headers and that AutoFilter is enabled for your table before applying custom text patterns.

Solution 1Recommended

Use Custom Text Filters with Wildcards

By using the 'Equals' text filter combined with wildcard characters, you can force Excel to recognize and enforce the leading space in your search criteria.

The standard search box inside the filter dropdown automatically trims leading spaces for convenience. To bypass this default behavior and perform an exact string match, you must use the Custom AutoFilter dialog along with the asterisk (*) wildcard.

1
Open Filter Options

Click the filter drop-down arrow located on the header of the column you want to sort.

2
Select Custom Filter

Hover your mouse over 'Text Filters' in the dropdown menu, then click on 'Equals...' to open the Custom AutoFilter dialog box.

3
Enter the Wildcard Pattern

In the criteria box next to 'equals', type an asterisk followed by your exact text pattern including the leading space, and ending with another asterisk. For example, type `* 99r*`.

4
Apply the Filter

Click 'OK'. Excel will now filter the table to show only the entries that contain the specific string with the required leading space.

Use Custom Text Filters with Wildcards
Wildcard Matching: The asterisk (*) represents any number of characters. Wrapping your text pattern in asterisks ensures that any characters before or after the target string are allowed, as long as the space immediately precedes your specific value.
Advanced Data Filtering

Filter Text and Handle Formatting Easily in WPS Spreadsheet

WPS Spreadsheet provides powerful filtering capabilities, including robust wildcard support and advanced custom auto-filters. This allows you to effortlessly extract precise data patterns, such as strings with leading spaces, without losing productivity.

  1. 1. Open Data File: Launch WPS Spreadsheet and open the document containing your table.
  2. 2. Enable AutoFilter: Select your data range, navigate to the 'Data' tab on the ribbon, and click 'AutoFilter'.
  3. 3. Access Text Filters: Click the filter arrow on your target column, navigate to 'Text Filters', and choose 'Equals'.
  4. 4. Apply Criteria: Enter your criteria including the required space and wildcard (e.g., `* 99r*`) and click 'OK' to view the filtered results.
Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formatting and formulas.Intuitive Custom AutoFilter dialog for exact wildcard and spacing matches.Free, lightweight, and features a familiar tabbed interface for seamless migration.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the standard Excel search box ignore my spaces?

The quick search box in the Excel filter menu is designed to automatically trim leading and trailing spaces to help users find matches more forgivingly. To mandate exact spacing, you must bypass the quick search and use the 'Custom Text Filters' option instead.

Can I filter to find cells that only consist of a single space?

Yes, you can use the Custom AutoFilter 'Equals' option and just type a single space. Alternatively, you can use the LEN function in an adjacent helper column to identify cells containing only space characters.

What wildcards can I use in Excel custom filters?

You can use the asterisk (*) to represent any series of characters, the question mark (?) to represent any single character, and the tilde (~) to escape a wildcard character if you actually need to search for a literal asterisk or question mark.