Fix an Excel Table with Numbers Stored as Text
Question details
Convert text-formatted numbers in an Excel table into numeric values so calculations and matches work.

- 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.
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.
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.
Select the affected cells in the table column, excluding the header.
Go to Data > Text to Columns. Choose Delimited and click Next.
Clear any selected delimiters, click Next, choose General as the column data format, and click Finish.
Test an affected formula or lookup. If needed, use Home > Number Format to display the converted values as numbers.

Use Excel's Convert to Number Warning
Use this quick option when Excel flags the selected cells as numbers stored as text.
Convert Text Numbers with the VALUE Function
Use a helper column when you want to inspect converted values before replacing the original column.
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. Open the workbook: In WPS Spreadsheet, open the Excel file and locate the column containing text numbers.
- 2. Enter the formula: In an empty helper column, enter =VALUE(A2), replacing A2 with the first affected cell, and press Enter.
- 3. Fill and check: Drag the fill handle down the helper column and check the results for conversion errors.
- 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.

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.




