Convert CSV Numbers Stored as Text in Excel
Question details
Convert numeric values imported from a CSV file as text into numbers that Excel can calculate and sort correctly.

- Product
- Microsoft Excel for Microsoft 365 Business
- Device & OS
- not provided
- Scenario
- A CSV file has been imported into Excel, and the user needs to use an affected column in calculations and numeric sorting.
- Observed behavior
- Imported values remain text after the cells are formatted as Number, causing calculations or sorting to behave incorrectly.
Identify the affected column and test a cell with =ISTEXT(A1), replacing A1 with the relevant cell reference. Keep identifiers such as ZIP codes or values with leading zeros as text if their exact appearance must be preserved.
Convert the column with Text to Columns in Excel
Use this method to convert an existing column of numeric text without splitting it into multiple columns.
In Excel, select the cells or column containing the numbers imported as text.
Select Data > Text to Columns, choose Delimited, and select Next.
Clear all delimiter checkboxes, check the data preview, and select Next.
Under Column data format, select General, then select Finish. Excel converts recognizable numeric text to numbers.

Set the column type when importing a CSV file
Use the import settings to prevent numeric columns from being treated as text in a future import.
Convert CSV text values to numbers in WPS Spreadsheet
Open the CSV in WPS Spreadsheet and use the VALUE function to convert recognizable numeric text in a separate column.
- 1. Open the CSV: In WPS Spreadsheet, select File > Open and choose the CSV file.
- 2. Enter a conversion formula: In an empty column, enter =VALUE(A2), replacing A2 with the first cell containing numeric text.
- 3. Fill the formula down: Select the formula cell and drag its fill handle down alongside the remaining values.
- 4. Keep the converted values: Copy the converted cells, then use Home > Paste Special > Values to paste the results as numbers. Save as an XLSX workbook if you want to retain spreadsheet formatting.

Frequently Asked Questions
Why doesn't changing the cell format to Number convert the values?
The Number format changes how numeric cells are displayed; it does not turn existing text into numbers. Select the affected cells and use Data > Text to Columns to convert recognizable numeric text.
How can I check whether an imported value is text?
Enter =ISTEXT(A1) in an empty cell, replacing A1 with the cell you want to test. TRUE means the value is stored as text.
Will converting text to numbers remove leading zeros?
It can. Keep ZIP codes, account numbers, and other identifiers as text when leading zeros are part of the value.
What if Excel still doesn't recognize the numbers?
Check for extra characters and verify that decimal and thousands separators match Excel's settings. In the Text to Columns wizard, use Advanced on the final screen to specify separators if needed.




