How to Filter Cells That Start With an Asterisk in Excel
Question details
Return Excel or WPS Office Spreadsheet cells whose values begin with an asterisk, either alone or followed by a space, without letting the asterisk behave as a wildcard.

- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a spreadsheet range and needs to filter or flag values that start with a literal asterisk, including entries such as '*', '* text', and similar.
- Observed behavior
- In many Excel and WPS Spreadsheets search functions, an asterisk is treated as a wildcard, so ordinary filters or lookup formulas may not return only literal leading asterisk cells.
Confirm the data range you want to test and check whether your WPS Office version supports the FILTER function. If FILTER is not available, use the IF/OR/LEFT helper-column method below.
Use FILTER and LEFT to return cells that start with an asterisk
Best for Excel 365 and WPS Office versions that support dynamic array functions; this returns a spill range of matching cells.
The asterisk is a wildcard in Excel and WPS Spreadsheets, so searching for "*" normally matches all non-empty text. Wrapping LEFT around the range tests only the actual first character and avoids wildcard interpretation.
Click an empty cell where you want the filtered list to begin, such as C1.
Type =FILTER(A1:A100,LEFT(A1:A100,1)="*") and press Enter.
To return a blank instead of an error when no cells match, use =FILTER(A1:A100,LEFT(A1:A100,1)="*","") instead.

Use IF, OR and LEFT to flag each matching cell individually
Use this row-by-row helper-column approach when FILTER is not available or you need to mark matches next to the original data.
Filter cells starting with an asterisk in WPS Office Spreadsheets
Open your workbook in WPS Office Spreadsheets and use LEFT-based formulas to isolate literal asterisk cells. WPS Office supports Excel formulas and .xlsx files, so you can apply the same FILTER or IF/OR/LEFT approach without changing tools.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, click Open, and select the .xlsx file containing the range to filter.
- 2. Select the output cell: Click an empty cell where the filtered results should appear, for example C1.
- 3. Enter the recommended formula: Type =FILTER(A1:A100,LEFT(A1:A100,1)="*") and press Enter. Use =FILTER(A1:A100,LEFT(A1:A100,1)="*","") to return a blank instead of an error.
- 4. Adjust the range as needed: Replace A1:A100 with your actual data range and confirm the results update automatically.

Frequently Asked Questions
Why can't I use a normal wildcard filter to find asterisks?
In Excel and WPS Spreadsheets, the asterisk is a wildcard character that represents any number of characters. A criterion like "*" therefore matches all non-empty text instead of finding literal asterisks. Use LEFT(A1:A100,1)="*" to test the actual first character.
Does this method also match cells starting with an asterisk followed by a space?
Yes. The formula =FILTER(A1:A100,LEFT(A1:A100,1)="*") matches any cell whose first character is an asterisk, including values such as "* text". The IF/OR formula shown above also explicitly tests for "* ".
What if my WPS Spreadsheets version does not support the FILTER function?
Use a helper column with =IF(OR(LEFT(A1,2)="* ",LEFT(A1,1)="*"),A1,"") and copy it down through the data range to flag each matching cell.
How do I return a blank instead of an error when no cells match?
Add the if_empty argument to FILTER: =FILTER(A1:A100,LEFT(A1:A100,1)="*",""). This returns an empty string when there are no matching cells.
Can I filter the data in place without using a formula?
Standard AutoFilter can be unreliable for literal leading asterisks because the wildcard is interpreted as a pattern. A formula or helper column that checks the first character with LEFT is the safer approach.




