How to Remove Dollar Signs from Text Values in Excel
Question details
The user needs to remove dollar signs from currency values that are incorrectly stored as text, which prevents standard numeric calculations.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Preparing imported or misformatted currency data for numerical calculations and conversions.
- Observed behavior
- Numbers containing dollar signs are being treated as text strings by the spreadsheet, preventing mathematical functions from working.
Before attempting to delete the dollar signs, verify whether the cells are genuinely stored as text or if they simply have a 'Currency' or 'Accounting' format applied. If it is merely a formatting setting, changing the cell format to 'General' or 'Number' will resolve the issue automatically.
Use Find and Replace to Strip the Dollar Sign
The quickest way to remove embedded dollar signs from text strings is by utilizing the built-in Find and Replace shortcut.
This method directly edits your original data by replacing the text character with nothing, effectively stripping the symbol away.
Select the affected column containing the text values. Right-click the selection, choose 'Format Cells', and set the category to 'General'.
Press the Ctrl+H keyboard shortcut to immediately open the Find and Replace dialog box.
In the 'Find what' field, type the '$' symbol. Leave the 'Replace with' field completely empty. Click on 'Options' and ensure that 'Match entire cell contents' is unchecked.
Click 'Replace All' to remove the dollar signs. If the numbers are still stored as text (indicated by a green triangle in the corner), select the cells, click the warning icon that appears, and choose 'Convert to Number'.
Use the SUBSTITUTE and VALUE Functions
If you prefer to keep your original text intact and generate clean numerical values in a new column, using a formula is the safest approach.
Clean Formatting Easily in WPS Spreadsheet
WPS Spreadsheet offers powerful data cleaning tools, including an advanced Find & Replace interface and text-to-column features, allowing you to instantly convert text-based currency into calculable numbers.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your text-formatted currency.
- 2. Select the data: Highlight the column or specific cells where the dollar signs need to be removed.
- 3. Launch Find & Replace: Press Ctrl+H to bring up the Replace dialog window.
- 4. Remove the symbols: Type '$' in the 'Find what' box, leave 'Replace with' blank, and click 'Replace All'.
- 5. Convert to Number: Click the small exclamation warning icon next to your newly cleaned cells and select 'Convert to Number'.
Frequently Asked Questions
Why does Excel show my currency numbers as text after importing data?
When importing data from external sources like CSV files, PDFs, or web pages, currency symbols or invisible extra spaces can cause the spreadsheet application to interpret the incoming data as standard text strings instead of numeric values.
Can I just change the cell format to 'Number' to remove the dollar sign?
No. If the dollar sign was manually typed or imported as part of a static text string, changing the cell's visual format will not remove it. You must use Find and Replace or a text-extraction formula to strip the character first.
How do I convert the text back to a number after removing the dollar sign?
Once the dollar sign is gone, you can click the small error icon next to the selected cells and choose 'Convert to Number'. Alternatively, you can copy an empty cell, select your data, choose Paste Special, and select 'Add' or 'Multiply' by 1.
