Data Import & Export

Fix an Excel Table with Numbers Stored as Text

Steve KSteve K Nov 18, 2026 870 views

Question details

Convert text-formatted numbers in an Excel table into numeric values so calculations and matches work.

Fix an Excel Table with Numbers Stored as Text
Product
Microsoft Excel
Device & OS
not provided
Scenario
Numbers were imported or copied into a table column and are needed for calculations or lookups.
Observed behavior
Values stored as text may not calculate or match numeric values as expected.
Before you start

Identify the affected column and check whether Excel shows a green triangle or a number-stored-as-text warning. Keep identifiers with meaningful leading zeros as text rather than converting them to numbers.

Solution 1Recommended

Convert the Table Column with Text to Columns

Use this method to convert a column of text numbers in place, including values without a visible warning.

Changing the cell format to Number alone does not convert existing text values.

1
Select the values

Select the affected cells in the table column, excluding the header.

2
Open the conversion wizard

Go to Data > Text to Columns. Choose Delimited and click Next.

3
Convert the column

Clear any selected delimiters, click Next, choose General as the column data format, and click Finish.

4
Check the results

Test an affected formula or lookup. If needed, use Home > Number Format to display the converted values as numbers.

Convert the Table Column with Text to Columns
Check number separators: If decimal or thousands separators differ from your Excel settings, use Advanced in the Text to Columns wizard to specify the separators before clicking Finish.
Convert text numbers in WPS

Convert Text Numbers with WPS Spreadsheet

Open your Excel workbook in WPS Spreadsheet and use a helper column with VALUE to convert text numbers before pasting the results back into the table.

  1. 1. Open the workbook: In WPS Spreadsheet, open the Excel file and locate the column containing text numbers.
  2. 2. Enter the formula: In an empty helper column, enter =VALUE(A2), replacing A2 with the first affected cell, and press Enter.
  3. 3. Fill and check: Drag the fill handle down the helper column and check the results for conversion errors.
  4. 4. Paste the converted values: Copy the helper cells, select the original data cells, and use Home > Paste > Paste Special > Values. Then retest the affected formulas or lookups.
Convert imported text numbers for calculations and lookupsOpen and save Microsoft Excel XLSX and XLS filesReview converted values before replacing the original columnUse familiar spreadsheet formulas and menus
microsoft office alternative - wps office

Frequently Asked Questions

Why didn't changing the cell format to Number fix the table?

The Number format changes how numeric values are displayed, but it does not convert existing text. Use Data > Text to Columns or the Convert to Number warning to change the underlying values.

How can I tell if an Excel number is stored as text?

Select the cell and look for a green triangle or a warning icon that identifies a number stored as text. You can also test the cell with =ISNUMBER(A2); FALSE means it is not stored as a number.

Why does a lookup fail when the values look identical?

A lookup may compare a numeric value with a text value, which can prevent an exact match. Convert the relevant text values to numbers, then check that the lookup uses matching data types.

Will converting text numbers remove leading zeros?

Yes. Numeric conversion removes leading zeros from the stored value. Keep codes or identifiers as text when those zeros are meaningful.