Excel Formulas to Remove Currency Symbols and Convert to Numeric Values

Cleaning imported financial data with mixed, non-standard currency symbols in Excel is notoriously frustrating, often halting critical reporting. When consolidating revenue figures from standard funding sources-such as government grants, venture capital, or international loans-analysts routinely face erratic formatting. Fortunately, automating this cleanup grants immediate clarity and accelerates executive decision-making.

Under the stipulation that your locale settings align with the target numeric output, utilizing formulas like NUMBERVALUE combined with SUBSTITUTE can rapidly strip prefix symbols like "$" or "€" into pure numerical values. Below, we provide the exact formula configurations to seamlessly streamline your financial ledger.

When importing financial data into Microsoft Excel from web pages, PDFs, text files, or external ERP systems, you often encounter a frustrating hurdle: currency values formatted as text. Instead of raw numbers that Excel can calculate, you get strings like "$1,250.50", "€ 450.00", "£99.99", or even mixed formats like "150.00 USD".

Because Excel treats these cells as text, any attempt to perform basic arithmetic-such as using the SUM or AVERAGE functions-returns zero or an error. To restore functionality to your spreadsheets, you must clean these currency symbols and convert the remaining characters back into true numeric values. This guide explores several highly effective Excel formulas and techniques to accomplish this task, ranging from basic solutions to advanced, dynamic array formulas.

Understanding the Root Cause: Text vs. Number Formatting

Before diving into the formulas, it is crucial to understand the difference between how Excel displays numbers and how it stores them:

• Formatted Numbers: If you type 1250.5 into a cell and apply Excel's built-in Currency formatting, the cell displays "$1,250.50". However, the underlying value remains the raw number 1250.5. Excel can easily use this cell in calculations.
• Hardcoded Text Strings: If your imported data contains the actual symbol character (e.g., "$") typed directly alongside the digits, Excel interprets the entire cell as a text string. The underlying value is literally "$1,250.50", rendering it mathematically useless.

Our goal is to strip away these textual obstacles and convert the clean digits into real, calculable numbers.

Method 1: The Basic SUBSTITUTE and VALUE Formula

If your dataset uses a single, consistent currency symbol (for example, the dollar sign "$"), you can use a combination of the SUBSTITUTE and VALUE functions to clean the data.

The Formula:

=VALUE(SUBSTITUTE(A2, "$", ""))

How It Works:

1. SUBSTITUTE(A2, "$", ""): This function searches the text in cell A2 for the "$" character and replaces it with an empty string (""), effectively deleting it. If cell A2 contained "$1250.50", the result of this step is the text string "1250.50".
2. VALUE(...): While the symbol is gone, Excel still treats "1250.50" as text. Wrapping the expression in the VALUE function forces Excel to convert this numeric text string into a genuine number (1250.50).

Method 2: Handling Multiple Currency Symbols (Nested Substitutions)

If your dataset contains a mix of different currencies-such as dollars ($), euros (€), and pounds (£)-a single substitution will not suffice. In this case, you can nest multiple SUBSTITUTE functions inside one another.

The Formula:

=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "$", ""), "€", ""), "£", ""))

How It Works:

Excel evaluates nested formulas from the inside out. First, it removes the "$" sign. Next, it takes that result and removes any "€" signs. Then, it removes any "£" signs. Finally, the outer VALUE function converts the completely cleaned text string into a standard number.

Method 3: Cleaning Web Data and Hidden Spaces (The TRIM and CLEAN Solution)

Data scraped or exported from web browsers often contains hidden characters that cause basic formulas to fail. The most notorious culprit is the non-breaking space (represented by HTML character code   or CHAR(160) in Excel), which standard deletion methods ignore.

To tackle stubborn spaces, trailing spaces, and hidden web characters alongside currency symbols, use this robust formula:

The Formula:

=VALUE(TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2, "$", ""), CHAR(160), ""))))

How It Works:

• SUBSTITUTE(A2, CHAR(160), ""): Target and remove non-breaking spaces specifically.
• CLEAN(...): Removes any non-printable control characters that might have carried over from database exports.
• TRIM(...): Removes any standard leading, trailing, or double spaces remaining in the text.

Method 4: Handling Regional Decimals with NUMBERVALUE

In many European countries, commas are used as decimal separators, and periods (or spaces) are used as thousands separators (e.g., "€ 1.250,50"). If your local computer settings expect the US standard (dots for decimals and commas for thousands), simply removing the currency symbol will cause Excel to misinterpret the value or throw a #VALUE! error.

To resolve regional formatting conflicts, use the NUMBERVALUE function, which allows you to explicitly define decimal and separator characters.

The Formula:

=NUMBERVALUE(SUBSTITUTE(A2, "€", ""), ",", ".")

How It Works:

The NUMBERVALUE function takes three arguments: the text to convert, the decimal separator used in that text, and the group (thousands) separator used in that text. In the formula above, we strip the "€" symbol, then tell Excel that any comma (",") in the text should be treated as a decimal point, and any period (".") should be treated as a thousands separator.

Method 5: Advanced Extract-Only-Numbers Formula (Excel 365 & 2021)

If your text strings are highly irregular-containing various currency symbols, codes like "USD", "EUR", or even random letters mixed with numbers (e.g., "Price: $120.50 per unit")-traditional substitution becomes impractical.

For modern Excel users, you can use a dynamic array formula that systematically inspects every character in a cell, keeps only the numeric digits and decimal points, and discards everything else.

The Formula:

=VALUE(CONCAT(IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1)+0, IFERROR(IF(MID(A2, SEQUENCE(LEN(A2)), 1)=".", ".", ""), ""))))

How It Works:

1. SEQUENCE(LEN(A2)): Generates an array of numbers from 1 up to the total length of the text string in A2.
2. MID(A2, ..., 1): Breaks down the text string into an array of individual, single characters.
3. +0: Attempts to add zero to each character. Numeric characters successfully convert to numbers (e.g., "5" + 0 = 5). Non-numeric characters (like symbols, letters, and spaces) result in a #VALUE! error.
4. IFERROR(..., IFERROR(IF(...=".", "."), "")): Catches those errors. It specifically checks if the failed character is a decimal point ("."). If it is, it keeps the decimal point; otherwise, it replaces the error with an empty string ("").
5. CONCAT(...): Re-assembles the array of allowed characters (digits and the decimal point) back into a single continuous text string.
6. VALUE(...): Converts the resulting string into an actual number.

Summary of Solutions

To help you choose the best formula for your specific scenario, review the reference table below:

Pro-Tip: Paste as Values

Once you have applied these formulas to clean your dataset, you may want to delete the original columns of raw, messy data. However, because your formulas reference those original cells, deleting them directly will result in a worksheet full of #REF! errors.

To safely clean up your workbook:

1. Select the column containing your newly calculated clean formulas.
2. Press Ctrl + C to copy the range.
3. Right-click on the destination cells (or the original data cells if you want to overwrite them).
4. Select Paste Special > Values (or click the paste icon with "123").

This replaces the dynamic formulas with static, calculated numerical values, allowing you to delete any redundant columns without breaking your calculations.