'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Excel Not Recognizing Currency or Percentage Values
Question details
The user needs to convert text-formatted currency or percentage data into numerical values so that mathematical operations and proper number formatting can be applied successfully.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing data from external sources, copying and pasting figures, or reapplying formats to financial data.
- Observed behavior
- Excel treats currency or percentage values as text rather than numbers. Number formatting fails to apply correctly, even after reapplying the format or copying the data to a new worksheet.
Before converting your data, ensure you select only a single column of affected cells at a time, as the Text to Columns tool cannot process multiple columns simultaneously.
Convert Text to Numbers Using Text to Columns
This method forces Excel to re-evaluate the data in the selected cells, converting text-formatted values back into recognizable numbers.
When data is imported from CSV files or external databases, numbers are often stored as text. The Text to Columns wizard can instantly parse these cells and convert them into standard numeric formats without needing complex formulas.
Highlight the specific column or range of cells containing the unrecognized currency or percentage values.
Navigate to the Data tab on the top ribbon and click on the 'Text to Columns' button located in the Data Tools group.
In the wizard window, select the 'Delimited' option and click 'Next'. On the following screen, clear all delimiter checkboxes (such as Tab, Space, or Comma) and click 'Next' again.
Leave the Column data format as 'General' and click 'Finish'. Your text values will now be converted to numbers, allowing currency and percentage formatting to apply correctly.
Use the Paste Special Multiply Method
Multiplying text-formatted numbers by 1 forces Excel to calculate the value, instantly converting it into a true number.
Resolve Data Formatting Issues Easily in WPS Spreadsheet
WPS Spreadsheet provides powerful data parsing and conversion tools. You can quickly fix text-formatted numbers and apply accurate currency or percentage formats natively, ensuring your financial data is always ready for calculation.
- 1. Open Your Spreadsheet: Launch WPS Office and open the file containing your unformatted data using WPS Spreadsheet.
- 2. Highlight the Data: Select the column or range of cells where the currency or percentage values are not being recognized.
- 3. Run Text to Columns: Go to the Data tab on the top menu and select 'Text to Columns'.
- 4. Apply Conversion: Follow the wizard by choosing Delimited, unchecking all delimiters, and clicking Finish to convert the text to numbers.
Frequently Asked Questions
Why does Excel import my numbers as text?
This commonly occurs when exporting data from ERP systems, web platforms, or CSV files. These systems often append hidden characters or apostrophes to prevent leading zeros from dropping, which forces Excel to read the incoming data as plain text.
How can I easily identify if a number is formatted as text?
By default, Excel aligns text to the left side of a cell and true numbers to the right. Additionally, cells containing numbers formatted as text will often display a small green triangle in the top-left corner, prompting a 'Number Stored as Text' warning.
Can I use a formula to fix unrecognized currency values?
Yes. You can use the VALUE function. Entering '=VALUE(A1)' in an adjacent cell will force Excel to read the text in cell A1 as a numerical value. You can then copy the result and use Paste Special > Values to replace the original text.
Will fixing the text-to-number issue alter my actual data?
No. Using the Text to Columns or Paste Special methods only changes the internal data type from text to numeric. It does not alter the actual digit values within the cell.
