Numbers and dates stored as text in Excel™: find and fix them
A column of figures that looks fine but will not add up is often text. The same goes for dates that sort in the wrong order. Anything that reads the sheet, whether a formula, a pivot or an assistant, may skip or misread these cells.
How to spot them
- Numbers sit left-aligned in the cell when you have not set the alignment.
- A small green triangle may appear in the corner of the cell.
- SUM returns less than you expect, and the status bar shows a count but no sum for the selection.
- Dates sort as "01/02, 01/03, 02/01" instead of in calendar order.
- One date column mixes formats, for example
05/11/2026beside5 Nov 2026.
Convert by hand
These steps apply to Excel™ in Microsoft 365. Work on a copy.
- Select the column.
- Choose Data → Text to Columns, then Finish without changing anything. Excel™ re-reads each cell and often converts numbers and dates.
- If that does not work, type 1 in a spare cell, copy it, select the problem cells, and use Paste Special → Multiply.
- For stray spaces, a helper column with
=TRIM(A2)removes them. Non-breaking spaces from web copies may needSUBSTITUTE. - After converting, set the column's format to Number or Date.
Check dates against one you know. A day-first date read as month-first looks valid and is wrong.
Find them across a sheet
AI Readiness Scanner and Fixer checks the active worksheet for numbers stored as text (TYP-01), dates stored as text or in mixed formats (TYP-02), and mixed value types in one column (TYP-03). Each finding says where it is and why it matters, and Show me selects the cells. The scan, explanations and Undo are free; applying a fix is paid.
Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. AI Readiness Scanner and Fixer is not affiliated with or endorsed by Microsoft.