How to Paste Currency Values as Numbers in Excel 365
Question details
The user needs to remove currency symbols from imported text and convert the data into usable numeric values for mathematical calculations.
- Product
- Excel 365
- Device & OS
- not provided
- Scenario
- Importing or copying data containing currency symbols where standard 'Paste Values Only' operations fail to yield valid numeric formats.
- Observed behavior
- Pasted currency values remain formatted as text strings, preventing mathematical functions and calculations from working correctly.
Identify the exact characters in your currency data (such as dollar signs, euro symbols, or extra spaces) to ensure you remove the correct text strings during conversion.
Use the NUMBERVALUE and SUBSTITUTE Formulas
This is the most precise method to strip out specific currency symbols and spaces, forcing the text to evaluate as a numerical value.
When data is imported from external sources, currency symbols are often hardcoded as text. Using Paste Values Only retains this text. The SUBSTITUTE function removes the symbol, and the NUMBERVALUE or VALUE function converts the result into a calculation-ready number.
Click on an empty cell adjacent to your original currency text data.
Type the formula =NUMBERVALUE(SUBSTITUTE(A1,"$ ","")). Replace 'A1' with your target cell, and change '"$ "' to match the exact currency symbol and spacing found in your text.
Press Enter to generate the converted number, then click and drag the fill handle at the bottom right of the cell to apply the formula down the column.
Copy the new column of converted numbers, right-click your original column, and select 'Paste Special' > 'Values' to permanently replace the text with valid numbers.
Use the Text to Columns Feature
Ideal for quickly cleaning entire columns of data without having to write formulas or create helper columns.
Process Currency Data Seamlessly in WPS Spreadsheet
WPS Office provides a highly compatible spreadsheet environment that easily manages data imports, formula conversions, and data cleaning tasks. You can seamlessly use the exact same formulas or Text to Columns features as you do in Microsoft Excel.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your imported currency text.
- 2. Apply the formula: Click on an empty cell next to the data and enter the formula =NUMBERVALUE(SUBSTITUTE(A1,"$ ","")).
- 3. Drag to fill: Press Enter, then click and drag the fill handle to apply the conversion across your entire data range.
- 4. Use Text to Columns alternatively: Highlight the column, go to the 'Data' tab, and click 'Text to Columns' to instantly format the text strings into real numbers.
Frequently Asked Questions
Why doesn't 'Paste Values Only' remove the currency symbol in Excel?
Paste Values Only strips out the dynamic cell formatting. However, if the original source data contained the currency symbol hardcoded as part of the text string, Paste Values Only will paste those exact text characters as-is.
How can I fix currency text that has trailing spaces instead of symbols?
You can use the TRIM function combined with the VALUE function. Entering =VALUE(TRIM(A1)) will strip out any extra invisible spaces and convert the remaining clean text into a valid mathematical number.
Can I use Power Query to clean this currency data?
Yes. You can import the data using Power Query, use the 'Replace Values' function to remove the currency symbol and spaces, change the column data type to 'Decimal Number', and then load it back into your spreadsheet.
