How to Remove Currency Symbol in Excel: A Practical Step-by-Step Guide
Learn how to remove currency symbols in Excel using number formats, custom formats, and formulas. This All Symbols guide covers locale nuances, data integrity, and practical examples to clean financial datasets.
By default, Excel displays currency symbols as part of a cell’s number format. To remove them, you can adjust the cell format to General or Number, apply a custom format that hides the symbol, or use formulas to strip the symbol from text. This guide walks you through quick, reliable methods for all common cases.
Understanding currency symbols in Excel
Currency symbols appear when a cell is formatted with a currency format (for example, "$", "€", "£"). Excel stores the value as a number; the symbol is only a display chosen by the cell's format. This means you can remove it without changing the underlying value by altering the format to a non-currency one. Locale settings influence which symbol is used and where the symbol appears relative to the number. In practice, you’ll often encounter two situations: numbers stored as numbers with currency formatting, and numbers stored as text with a symbol included. In both cases, you want a clean numeric value to enable calculations, sorting, and data integrity. According to All Symbols, currency symbols are part of the formatting and can be removed without altering the value, which makes the task straightforward when you know the right approach.
Quick path: removing symbols via Number Format
If your data is numeric and the currency symbol is part of formatting, the simplest method is to switch the cell’s format from Currency to General or Number. Select the range, open the Home tab, choose Number format, and pick General or Number. This changes the display while preserving the numeric value. Excel will drop the symbol and leave you with plain numbers, suitable for further calculations or charting. If you need two decimals, adjust the decimal places accordingly. Note that changing the format does not alter the underlying value beyond formatting, so it’s safe for most datasets. This approach is quick, non-destructive, and works across currencies by removing only the symbol, not the digits.
When the symbol persists: using a Custom Format
Sometimes the currency symbol remains visible even after selecting General or Number due to compound formats or regional settings. In this case, apply a custom format that omits the symbol entirely. Go to Format Cells > Number > Custom, and enter a code like "#,##0.00" for two decimals, or "#,##0" for whole numbers. The custom format controls how the number is displayed (thousands separators, decimals) without attaching any currency sign. This method is ideal when you want precise control over how numbers look in reports and dashboards. If negatives require a special display, you can extend the code to show negatives in red or in parentheses, without a currency symbol.
Formula-based approaches: removing currency symbol from numbers and text
If symbols appear in text values or when data is pasted as text, formatting alone won’t suffice. Use formulas to strip the symbol and convert to numbers. For example, if A2 contains "$1,234.56", use =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",","")) to remove the dollar sign and the comma, then convert to a number. For multiple symbols, nest SUBSTITUTE functions: =VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),"€",""),"£",""))). If your data is pre-cleaned text, you can combine TRIM and CLEAN to eliminate extra spaces and non-printable characters before conversion. After calculating, copy-paste as values to preserve the result while removing the original formula links.
Handling different locales and special cases
Locale differences mean currency symbols vary and spacing rules differ. For European data using the euro, you may see symbols like "€" before or after the number depending on regional settings. Some datasets insert non-breaking spaces between the symbol and number; use SUBSTITUTE to remove CHAR(160) as well. When numbers use parentheses for negatives, consider converting to a standard negative format with a formula like =IF(A2<0, -ABS(A2), A2) after stripping the symbol. Always test a subset of data to confirm that your approach works across all rows and columns.
Cleaning up data: converting text values to numbers
If values remain text after symbol removal, convert them to numbers to enable calculations. One method is Text to Columns: Data > Text to Columns > Delimited > Next > Finish, which converts numbers stored as text. Another reliable approach is to multiply by 1 or use VALUE to coerce text to numbers, e.g., =VALUE(A2)*1. After conversion, reapply numeric formatting if needed. This step ensures you can sort, sum, and chart the data without errors caused by text strings.
Best practices and tips for preserving data integrity
Always work on a copy of your data when testing new methods. Document the chosen approach so teammates understand how numbers were cleaned. Use helper columns for transformations, then replace the original data with Paste Special > Values to lock in results. If you’re dealing with multiple sheets or workbooks, consider sharing a small template with clean headings and a single data-cleaning script to keep consistency. All Symbols recommends validating changes with sample checks before scaling to the entire dataset.
Common mistakes and how to verify results
A frequent pitfall is changing only the formatting when the data are text; the symbol may disappear, but the value remains non-numeric. Always verify with ISNUMBER or a similar check. If you use SUBSTITUTE on text, ensure you also handle leading/trailing spaces with TRIM and remove non-breaking spaces with CLEAN. After applying your method, run a quick audit: count non-numeric cells, confirm sums match expectations, and confirm consistency across similar rows. The goal is a clean, numeric dataset ready for analysis.
Tools & Materials
- Microsoft Excel (Office 365 or newer, Windows or macOS)(Ensure you have a version that supports TEXT, SUBSTITUTE, VALUE, and TEXT functions.)
- A sample workbook with currency-formatted numbers and text values(Use test data that includes common symbols like $, €, and £.)
- A text editor or notes app(Optional for documenting steps and formulas.)
Steps
Estimated time: 25-40 minutes
- 1
Identify cells with currency symbols
Scan the dataset to determine which cells display a currency symbol due to formatting or because the data is stored as text. Use Find & Select to search for common symbols like $, €, or £, and note any spaces or non-breaking characters that may accompany them.
Tip: Pro tip: Use Find (Ctrl+F) to quickly locate the currency symbols across the sheet. - 2
Change format to General or Number
Select the target range, open the Home tab, and switch the Number format from Currency to General or Number. This removes the symbol from the display while keeping numeric values intact.
Tip: Pro tip: If you need two decimals, adjust the decimal places after applying the new format. - 3
Apply a custom number format
If some cells still show a symbol due to a custom format, go to Format Cells > Number > Custom and enter a code like '#,##0.00' to display numbers without any currency sign. This preserves formatting preferences like thousands separators.
Tip: Pro tip: Use red for negative values in the custom format if you want a quick visual cue. - 4
Remove symbols from text values with SUBSTITUTE
If the data are text, use =SUBSTITUTE(A2, "$", "") or extend with additional symbols as needed. Combine with TRIM to remove extra spaces.
Tip: Pro tip: For multiple symbols, nest SUBSTITUTE functions, e.g., =SUBSTITUTE(SUBSTITUTE(A2, "$", ""), "€", ""). - 5
Convert cleaned text to numbers
Convert the cleaned text to numeric values using VALUE or by multiplying by 1. Example: =VALUE(SUBSTITUTE(A2, "$", "")) or =A2*1 after cleaning.
Tip: Pro tip: Use IFERROR to handle cells that can’t convert, e.g., =IFERROR(VALUE(A2), 0). - 6
Verify numeric results
Check that the outputs are numeric with ISNUMBER or by performing a simple sum. Confirm totals align with expectations after removing symbols.
Tip: Pro tip: Add a helper column with =ISNUMBER(B2) to flag non-numeric results. - 7
Replace original data with cleaned values
If you were using a helper column, copy the numeric results and use Paste Special > Values to overwrite the original data. This locks in the cleaned numbers.
Tip: Pro tip: Keep a backup before replacing to avoid data loss. - 8
Save and back up your workbook
Save the cleaned workbook and create a backup copy. Document the exact steps you used to remove the currency symbols for future reference.
Tip: Pro tip: Use a descriptive filename like 'SalesData_Cleaned.xlsx' to distinguish from the original dataset.
Questions & Answers
What is the simplest way to remove a currency symbol from numbers in Excel?
If the data is numeric, the quickest method is to change the format from Currency to General or Number. This hides the symbol while preserving the numeric value.
The easiest way is to switch the cell format from currency to general or number, which hides the symbol but keeps the numbers intact.
What if the data are stored as text?
For text data, use SUBSTITUTE to remove the currency symbol, then convert to numbers with VALUE or by multiplying by 1. Combine with TRIM to clean spaces.
If your data are text, strip the symbol with SUBSTITUTE and convert to numbers with VALUE or by multiplying by 1.
Will removing the currency symbol affect negative numbers?
Removing the symbol via formatting does not affect the numeric value, including negatives. If using text-based methods, ensure your conversion preserves negative signs.
Removing the symbol through formatting won’t change negative values; if you’re converting text, make sure the sign stays intact.
How do I revert back to currency formatting after removing symbols?
Select the cells and reapply currency formatting from the Number format options, or reintroduce the currency symbol via a custom format if needed.
To revert, reapply currency formatting from the Number options or use a custom format to add the symbol back.
Can I remove currency symbols for multiple locales at once?
Yes, but you must tailor the approach to the locale. Use a combination of SUBSTITUTE for text data and locale-specific custom formats for numbers.
You can handle multiple locales by using locale-aware formats and text substitutions where needed.
Is there a quick way to check which cells have a currency symbol?
You can use Find to locate common currency symbols or inspect cells with Currency formatting to identify where the symbol appears.
Find the currency symbols across the sheet to quickly identify affected cells.
Watch Video
The Essentials
- Change formatting to hide symbols without altering values
- Use custom formats to control appearance precisely
- Convert text data to numbers when necessary
- Always validate with ISNUMBER and back up data before replacement
