AppGarden AI Readiness Scanner and Fixer logo AppGarden

AI Readiness Scanner and Fixer

Numbers and dates stored as text in Excel™: find and fix them

By Paul Kemp · Published

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

Convert by hand

These steps apply to Excel™ in Microsoft 365. Work on a copy.

  1. Select the column.
  2. Choose Data → Text to Columns, then Finish without changing anything. Excel™ re-reads each cell and often converts numbers and dates.
  3. If that does not work, type 1 in a spare cell, copy it, select the problem cells, and use Paste Special → Multiply.
  4. For stray spaces, a helper column with =TRIM(A2) removes them. Non-breaking spaces from web copies may need SUBSTITUTE.
  5. 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.

See the scanner for Excel™

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.