How to detect numbers and dates stored as text in excel
- Step 1Export or save your file as .xlsx — The inspector accepts `.xlsx` only — it does not parse
.csv,.xls, or.ods. If your data is currently a CSV, open it in Excel and Save As → Excel Workbook (.xlsx) first, or audit the CSV directly with a CSV-side tool. A web/ERP export is usually already.xlsx. - Step 2Drop the workbook onto the tool above — SheetJS parses the file in your browser tab. Nothing is uploaded. The parser reads all sheets named in the workbook's
SheetNames, including ones that are hidden, so a stray text-number on a hidden lookup sheet is caught too. - Step 3Let the scan type-check every cell — For each cell the tool reads SheetJS's stored type (
t). Only cells stored as text (t === "s") are candidates. Already-numeric (n) and already-date cells are correct and are skipped — they will never appear in the report. - Step 4Read the mismatch report — The summary lists each flagged cell as
[Sheet] Cell: stored as text, looks like number/date ("value"). The first 100 mismatches are shown inline in the report text; the full result object keeps up to 500. A count panel above the report shows the total found. - Step 5Fix the flagged cells (in Excel or a converter) — This tool reports, it does not rewrite the file. To fix: in Excel select the flagged range, click the green-triangle warning → 'Convert to Number', or use Data → Text to Columns → Finish on the column. For a bulk programmatic fix, route the column through the formula-to-value tool or re-import cleanly.
- Step 6Re-scan to confirm zero mismatches — After fixing, drop the corrected workbook back onto the inspector. A clean file returns 'No format mismatches found. All numeric and date values appear to be stored with the correct type.' That clean result is your proof the audit is closed before you hand the file off.
What the inspector flags vs. what it ignores
The detector only inspects cells whose SheetJS stored type is text. The pattern tests below run on the trimmed cell value. Behaviour is exact to the implementation in excel-analysis.ts.
| Cell content | Stored type | Flagged? | Suggested type |
|---|---|---|---|
1234 typed as text (leading apostrophe '1234) | text | Yes | number |
1,234.56 as text (thousands comma) | text | Yes — commas stripped before the test | number |
-50 as text | text | Yes | number |
2024/01/15 as text | text | Yes | date |
01-02-2024 as text | text | Yes | date |
1234 already stored as a real number | number | No — correct already | — |
SKU-1234 (letters + digits) | text | No — fails the numeric pattern | — |
(1,234.00) accounting negative in parens | text | No — parentheses fail the numeric pattern | — |
| Empty / whitespace-only cell | text | No — blank cells are skipped | — |
Tool facts at a glance
Concrete capabilities and limits for the Format Inspector. Tier figures are the Excel-family limits from tier-limits.ts.
| Property | Value |
|---|---|
| Accepted input | .xlsx only (no CSV / XLS / ODS) |
| Engine | SheetJS (xlsx), runs in the browser |
| Output | Text report + a findings count panel (no file is rewritten) |
| Options / settings | None — single-click scan, no toggles |
| Auto-fix / convert button | No — detection only; you fix in Excel or a sibling tool |
| Free tier | 5 MB / 10,000 rows / 1 file |
| Pro tier | 50 MB / 100,000 rows / 5 files |
| Inline mismatches shown | first 100 in the report (up to 500 retained in the result object) |
| Privacy | 100% local; no upload, no server processing of cell content |
Cookbook
Real audit runs against the kinds of workbooks that hide text-stored numbers and dates. Report lines are shown verbatim in the inspector's [Sheet] Cell: ... format.
Leading-apostrophe numbers from a web-form export
A signup-form export wrote every quantity with a leading apostrophe so Excel would not 'helpfully' reformat it. Visually the column is full of numbers; underneath, every cell is text. The inspector catches the lot and tells you exactly which cells to convert.
Workbook: orders_export.xlsx (Sheet 'Orders')
Column C 'Qty' values appear as: 12, 7, 144, 3
But each cell was written as '12 (leading apostrophe = text)
Inspector report:
Found 4 format mismatch(es):
[Orders] C2: stored as text, looks like number ("12")
[Orders] C3: stored as text, looks like number ("7")
[Orders] C4: stored as text, looks like number ("144")
[Orders] C5: stored as text, looks like number ("3")Thousands-comma figures that fail SUM silently
A finance export formatted amounts as text with thousands separators. SUM returns a number far too low because most cells are ignored. The detector strips the commas internally before testing, so 1,234.56 is correctly recognised as a text-stored number.
Sheet 'P&L', column D 'Amount':
1,234.56 (text)
98,000 (text)
12.00 (text)
450 (real number — typed directly)
Inspector report:
Found 3 format mismatch(es):
[P&L] D2: stored as text, looks like number ("1,234.56")
[P&L] D3: stored as text, looks like number ("98,000")
[P&L] D4: stored as text, looks like number ("12.00")
(D5 is a real number — correctly NOT flagged)Mixed text dates and text numbers in one workbook
A legacy system dumped a sheet where the date column and an ID column are both text. One scan separates the two: dates are flagged 'looks like date', numbers 'looks like number', so you can fix each column with the right Excel action.
Sheet 'Tickets':
A2 = 2024/03/01 (text) -> date
A3 = 2024/03/02 (text) -> date
B2 = 5021 (text) -> number
B3 = 5022 (text) -> number
Inspector report:
Found 4 format mismatch(es):
[Tickets] A2: stored as text, looks like date ("2024/03/01")
[Tickets] A3: stored as text, looks like date ("2024/03/02")
[Tickets] B2: stored as text, looks like number ("5021")
[Tickets] B3: stored as text, looks like number ("5022")A genuinely clean workbook
After converting the text cells in Excel and re-saving, a re-scan returns the all-clear message. This is the result you want before sending a file to a downstream pivot or BI tool.
Workbook: orders_export_fixed.xlsx (all Qty / Amount cells converted to real numbers) Inspector report: No format mismatches found. All numeric and date values appear to be stored with the correct type.
SKU codes correctly left alone
An identifier column holds values like SKU-1234 and INV2024. Because these contain letters, they fail the numeric pattern and are NOT flagged — the inspector won't pester you to 'fix' codes that are meant to be text.
Sheet 'Catalog', column A 'SKU':
SKU-1234 (text — correct as text)
INV2024 (text — correct as text)
00045 (text — purely digits -> WILL be flagged)
Inspector report:
Found 1 format mismatch(es):
[Catalog] A4: stored as text, looks like number ("00045")
(SKU-1234 and INV2024 are NOT flagged — they contain letters)Edge cases and what actually happens
CSV file dropped instead of XLSX
RejectedThe Format Inspector accepts .xlsx only. A .csv has no per-cell type metadata for SheetJS to compare against — every value in a CSV is text by definition, so the audit would be meaningless. Open the CSV in Excel and Save As an Excel Workbook first, then scan. To clean CSVs directly, use a CSV-side tool such as the CSV deduplicator.
Leading-zero ID flagged as a number
ExpectedA value like 00045 or 0123 matches the numeric pattern, so it is reported as 'looks like number'. That is correct detection — but it may be intentional text (a zero-padded ID). The inspector flags it; you decide. If the column is meant to stay text, leave it; converting to a real number would drop the leading zeros.
Accounting negatives in parentheses not flagged
By design(1,234.00) — the accounting style for negatives — does not match the numeric pattern -?\d[\d,]*\.?\d* because of the surrounding parentheses, so it is NOT flagged even though it is a text-stored number. Find-and-replace the parentheses with a minus sign in Excel first, then re-scan to catch them.
Date in textual form like '15 Jan 2024'
Not detectedThe date detector only matches numeric date shapes (d/d/d, d-d-d, d.d.d). A spelled-out month (15 Jan 2024, January 15, 2024) does not match and is not flagged. These are still text dates that will break BI date logic — convert them in Excel (or standardize them) separately.
Ambiguous date like 03/04/2024
Flagged as date (no locale judgement)03/04/2024 matches the date pattern and is flagged as 'looks like date', but the inspector makes no decision about whether it is 3 April or 4 March. It only tells you the cell is a text date; the day/month interpretation is up to Excel's locale when you convert it. Decide the intended order before converting.
Report capped at 100 inline lines
Truncated displayIf a sheet has thousands of text-stored numbers, the report text shows the first 100 lines and appends '... and N more.' The findings object keeps up to 500 entries, and the count panel shows the true total. For very large messes, fix at the column level in Excel rather than cell by cell.
Hidden sheets are scanned too
SupportedThe scan iterates every name in the workbook's SheetNames, including hidden and very-hidden sheets. A text-stored number lurking on a hidden lookup tab will appear in the report with its sheet name — useful, because those are exactly the cells you would otherwise never see.
Number already stored correctly
PreservedCells whose stored type is already numeric (t === "n") are skipped entirely — they are correct. The inspector never 'flags' a real number, so a clean column produces zero noise. This is why a fully-converted workbook returns the all-clear message.
Percentage or currency-symbol text like '$50' or '50%'
Not detectedA leading $ or trailing % breaks the numeric pattern, so $50 and 50% are not flagged even though they are text. Strip the symbol in Excel (or convert the column with Text to Columns) first; once the cell is bare digits it will be caught on a re-scan.
Very large workbook over the tier limit
Rejected (size limit)Free tier caps the Excel family at 5 MB / 10,000 rows. A larger workbook is rejected before scanning. Upgrade to Pro (50 MB / 100,000 rows) or Pro-media (200 MB / 500,000 rows) for big finance exports, or split the workbook by sheet and scan each part.
Frequently asked questions
Does this tool fix the numbers, or just find them?
It finds them. The Format Inspector is a read-only audit: it produces a report of every cell that is stored as text but looks like a number or date, with the exact sheet and A1 address. It does not rewrite your workbook. To fix, select the flagged range in Excel and use the green-triangle 'Convert to Number', or Data → Text to Columns → Finish on the column. The inspector's job is to hand you a precise to-fix list.
Why does Excel store numbers as text in the first place?
The usual causes are: importing a CSV without column-type hints, cells that begin with a leading apostrophe ('1234), copy-paste from a web page or PDF, exports from legacy ERP/accounting systems that quote everything, and columns that were explicitly set to Text format before data was typed. Once stored as text, the values break SUM, AVERAGE, comparisons, lookups, and pivot aggregation.
What file types can I upload?
.xlsx only. The inspector compares each cell's stored type against its value, which requires the per-cell type metadata that lives in an XLSX workbook. CSV has no such metadata (every value is text), and XLS/ODS aren't parsed here. If your data is a CSV, open it in Excel and Save As an Excel Workbook first.
Does it scan all the sheets or just the active one?
All of them. The scan iterates every sheet named in the workbook, including hidden and very-hidden sheets. So a stray text-stored number on a hidden lookup tab — the kind you'd never spot manually — still shows up in the report with its sheet name.
Will it flag my SKU codes or order numbers?
Only if they are purely digits. A code with any letters (SKU-1234, INV2024) fails the numeric pattern and is left alone. But a purely numeric ID stored as text (00045) does match and will be flagged — that may be intentional (zero-padded IDs), so the tool flags it and lets you decide whether to convert.
Are my financial figures uploaded anywhere?
No. The entire scan runs in your browser tab using SheetJS. Cell values, sheet names, and the report never leave your machine. On the Free tier there is no upload and no account needed. The only thing that can be recorded server-side for signed-in users is a usage counter — never the content of your file.
How many cells can it report?
The report text shows the first 100 mismatches inline and then '... and N more.' if there are extras. The full findings object retains up to 500 entries, and the count panel above the report shows the true total mismatch count regardless. For thousands of mismatches, fix at the column level in Excel rather than cell by cell.
Does it detect text-stored dates as well as numbers, and what about spelled-out months?
Yes. There are two detectors. The number detector matches digit-and-comma values; the date detector matches numeric date shapes like 2024/01/15, 01-02-2024, or 1.2.2024, labelling each flagged cell 'looks like number' or 'looks like date'. But spelled-out months don't match — a value like '15 January 2024' or 'Jan-24' isn't flagged even though it's a text date. Convert those in Excel, or run the column through the date standardizer, separately.
Are there any options or settings to configure?
No. The Format Inspector has no toggles or settings — it's a single-click scan. You drop the .xlsx, it walks every cell, and you get the report. Other Excel tools (like the case normalizer or unit converter) have option panels; this one deliberately doesn't, because detection is the same regardless of file.
What's the largest file I can scan?
On the Free tier, 5 MB and 10,000 rows. Pro raises that to 50 MB / 100,000 rows, Pro-media to 200 MB / 500,000 rows, and Developer to 500 MB with unlimited rows. A file over your tier's limit is rejected before scanning; split the workbook by sheet if you need to stay under a cap.
After I fix the cells, how do I confirm the file is clean?
Re-scan it. Drop the corrected workbook back onto the inspector. A fully-fixed file returns 'No format mismatches found. All numeric and date values appear to be stored with the correct type.' That all-clear message is your proof the audit is closed before the file goes to a pivot, BI import, or a colleague.
What other Excel tools pair well with this audit?
Once you know which cells are wrong: use the error locator to find #REF!/#VALUE! errors, the formula highlighter to see formulas vs hardcoded values, and the date standardizer to normalise mixed date formats. For text-date columns, the standardizer is the natural follow-up to this scan.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.