How to Filter Excel Section Numbers That Begin with 1
Question details
The user needs to filter a column of section numbers (e.g., 1, 1.1, 1.1.1) to find all entries starting with the number 1.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Applying a text filter to a column containing hierarchical section numbers to display specific sub-sections.
- Observed behavior
- The 'Begins with 1' text filter fails to capture all matching values because Excel stores whole numbers (like 1) as numeric values and hierarchical numbers (like 1.1) as text.
Check the formatting of your section numbers column by looking at their cell alignment; by default, numbers align to the right and text aligns to the left. Expanding the column width can help you clearly see these default alignments to identify mixed formats.
Use Wildcard Search in the Filter Menu
A quick and effective way to bypass the mixed format issue is by using a wildcard character directly in the filter search box.
This method works seamlessly because the search box treats the input as a search string, capturing both text and numeric entries that match the pattern without requiring you to alter the underlying data.
Click the filter drop-down arrow on the header of your Section column.
Find the Search box located inside the filter drop-down menu.
Type '1*' (the number 1 followed by an asterisk) into the search box.
Click OK to apply the filter. The list will now display all values beginning with 1, regardless of their data format.

Use a Helper Column with the SEARCH Function
If you need to separate parent sections from sub-sections reliably, using a formula to identify specific characters is a robust solution.
Convert All Entries to Text Format
Standardizing the entire column to Text format ensures that standard text filters like 'Begins with' work consistently across all data.
Easily Filter and Format Complex Data with WPS Spreadsheet
WPS Office Spreadsheet provides powerful filtering tools and seamless text-to-number conversions, making it incredibly simple to organize hierarchical section numbers and complex datasets without formatting errors.
- 1. Open your file: Launch WPS Spreadsheet and open the document containing your section numbers.
- 2. Enable AutoFilter: Highlight your header row, navigate to the 'Data' tab, and click 'AutoFilter' to enable the drop-down menus.
- 3. Apply wildcard search: Click the filter arrow on your target column and type '1*' in the search bar to instantly display all matching records.

Frequently Asked Questions
Why does the 'Begins with' filter ignore some of my numbers?
The 'Begins with' filter is specifically designed as a Text Filter. If some of your section numbers (like '1' or '2') are stored as standard numeric values rather than text strings, the program will exclude them from text-based filter results.
How can I format a column as text before entering data?
Highlight the entire column, right-click and choose 'Format Cells'. Go to the Number tab, select 'Text' from the category list, and click OK. Any numbers typed into this column afterward will be strictly stored as text.
Can I use wildcards to filter sections that end with a specific number?
Yes, you can use the asterisk wildcard at the beginning of your search. Entering '*1' in the filter search box will display all entries that end with the number 1, regardless of what precedes it.
Is there a quick way to convert an entire column of numbers to text without pressing F2 for each cell?
Yes, you can use the 'Text to Columns' feature. Select the column, go to the Data tab, click 'Text to Columns', click 'Next' twice to reach the third step, select 'Text' as the Column data format, and click 'Finish'.




