Data Import & Export

Convert CSV Numbers Stored as Text in Excel

Khadija KhanKhadija Khan Nov 20, 2026 869 views

Question details

Convert numeric values imported from a CSV file as text into numbers that Excel can calculate and sort correctly.

Convert CSV Numbers Stored as Text in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the affected column

In Excel, select the cells or column containing the numbers imported as text.

2
Open Text to Columns

Select Data > Text to Columns, choose Delimited, and select Next.

3
Leave delimiters unchecked

Clear all delimiter checkboxes, check the data preview, and select Next.

4
Convert the values

Under Column data format, select General, then select Finish. Excel converts recognizable numeric text to numbers.

Convert the column with Text to Columns in Excel
Check special values: Conversion can remove leading zeros. Decimal and thousands separators must also match the settings Excel uses to recognize the values.
Work with CSV data in WPS

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. 1. Open the CSV: In WPS Spreadsheet, select File > Open and choose the CSV file.
  2. 2. Enter a conversion formula: In an empty column, enter =VALUE(A2), replacing A2 with the first cell containing numeric text.
  3. 3. Fill the formula down: Select the formula cell and drag its fill handle down alongside the remaining values.
  4. 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.
Convert numeric text for calculations and sortingOpen and work with Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and menusWork with CSV files in a free office suite
microsoft office alternative - wps office

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.