Data Import & Export

How to Remove Asterisks from Multiple Excel Cells Without Removing Numbers

Aamir Naveed Akram Sep 28, 2026 869 views

Question details

The user needs to remove asterisks from multiple Excel cells in bulk without deleting the numerical values.

How to Remove Asterisks from Multiple Excel Cells Without Removing Numbers

Product: Excel
Device & OS: not provided
Scenario: Cleaning up exported financial data, such as dollar amounts, that contain unwanted asterisks after being imported into a spreadsheet.
Observed behavior: The presence of asterisks prevents the spreadsheet software from recognizing the cell contents as numerical values, which hinders calculations and formatting.

Before you start

Ensure you have selected only the specific cells containing the data you want to clean, as applying wildcard operations to the entire worksheet might unintentionally alter other formulas or essential text.

Solution 1Recommended

Use Find and Replace with a Tilde (~) Wildcard

This is the safest and most efficient method to remove asterisks in bulk. The tilde forces the spreadsheet to treat the asterisk as a literal character rather than a wildcard that deletes everything.

In spreadsheet software, the asterisk (*) is commonly used as a wildcard character that represents any number of characters. If you attempt a standard Find and Replace searching for just an asterisk, the software assumes you want to find and delete the entire content of the cell. Using the tilde (~) escapes this behavior.

Select the Affected Cells

Click and drag to highlight all the cells containing the imported numbers with asterisks.

Open Find and Replace

Press Ctrl + H on your keyboard to instantly open the Find and Replace dialog box.

Enter the Escaped Asterisk

In the 'Find what' input field, type a tilde followed by an asterisk (~*).

Replace and Clean

Leave the 'Replace with' input field completely blank, and then click the 'Replace All' button.

Use Find and Replace with a Tilde (~) Wildcard

Format Cleaned Data: After removing the asterisks, format the cleaned values as Accounting or Number so they are properly recognized in your calculations.

Solution 2

Remove Asterisks Using the SUBSTITUTE Formula

If you want to keep the original raw data intact and generate a clean list of numbers in a separate column, using the SUBSTITUTE function is the best alternative approach.

Efficient Data Cleaning Made Easy

Clean Imported Data Faster with WPS Spreadsheet

WPS Spreadsheet provides powerful and highly compatible Find and Replace tools that perfectly mirror Microsoft Excel, making it incredibly easy to clean imported data, remove asterisks, and format numbers correctly.

1. Select Your Cells: Open your workbook in WPS Spreadsheet and highlight the cells containing the unwanted asterisks.
2. Open the Replace Dialog: Press the Ctrl + H shortcut on your keyboard to bring up the Find and Replace dialog.
3. Type the Escaped Asterisk: Input ~* into the 'Find what' box and ensure the 'Replace with' box remains totally empty.
4. Execute Replace All: Click the 'Replace All' button to instantly remove the asterisks while preserving your numbers.

Seamlessly handles Find and Replace with wildcard characters like the tilde.100% format compatibility with Microsoft Excel (.xlsx, .xls) files.Free, lightweight, and operates swiftly even when handling massive datasets.Intuitive one-click cell formatting for accounting and currency.

microsoft office alternative - wps office

Frequently Asked Questions

Why does entering just an asterisk in Find and Replace delete everything in the cell?

In most spreadsheet applications, an asterisk (*) is recognized as a wildcard character that stands for any sequence of characters. Searching for just an asterisk tells the software to find the entire content of the cell and replace it with whatever is in the 'Replace with' field.

How do I type the tilde (~) character on my keyboard?

On a standard US English keyboard, the tilde key is usually located in the top left corner, just below the Escape (Esc) key. You can type it by holding down the Shift key and pressing that key (Shift + `).

Why do my numbers still behave like text even after removing the asterisks?

Sometimes imported data retains a text format natively. To fix this, select the cleaned cells, click the small warning icon that appears next to them, and select 'Convert to Number'. Alternatively, you can copy a blank cell, highlight your data, choose Paste Special, select 'Add', and click OK to force a number conversion.

Excel & Data Problems