Function Problems

How to Filter Cells That Start With an Asterisk in Excel

Huda QurayshiHuda Qurayshi Nov 13, 2026 870 views

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.

How to Filter Cells That Start With an Asterisk in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the output cell

Click an empty cell where you want the filtered list to begin, such as C1.

2
Enter the FILTER formula

Type =FILTER(A1:A100,LEFT(A1:A100,1)="*") and press Enter.

3
Handle no-match results

To return a blank instead of an error when no cells match, use =FILTER(A1:A100,LEFT(A1:A100,1)="*","") instead.

Use FILTER and LEFT to return cells that start with an asterisk
Literal asterisk check: The LEFT function inspects the actual first character, so this formula matches cells beginning with "*" or "* " without wildcard side effects.
WPS Office Spreadsheets

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. 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, click Open, and select the .xlsx file containing the range to filter.
  2. 2. Select the output cell: Click an empty cell where the filtered results should appear, for example C1.
  3. 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. 4. Adjust the range as needed: Replace A1:A100 with your actual data range and confirm the results update automatically.
Uses the same LEFT and FILTER formulas as Excel for a familiar workflowOpens and saves .xlsx files with full Excel formula compatibilityFree, lightweight Office suite with spreadsheet, writer, and presentation appsAvoids wildcard matching issues by checking the first character directly
microsoft office alternative - wps office

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.