How to Fix Numbers Stored as Text in Excel

Numbers stored as text look like numbers, but Excel treats them as text. The symptoms are familiar: SUM adds up to 0, SUMIF/COUNTIF find nothing, sorting puts “100” before “20”, and lookups fail on values that look identical. The fix is not to retype the numbers — it is to find out why they are text, then convert them in a way that matches that cause.

Verification note: These steps describe standard Excel behaviour. They have not yet been verified against a live Excel 365 file for this site, and no sample workbook is published yet. Work on a copy of your file and check a few converted values before converting a whole column.

Quick test: is it a number or text?

In a blank cell, type (replacing A2 with your problem cell):

=ISNUMBER(A2)

TRUE — a real number. Your problem is elsewhere.
FALSE — text (or blank). If the cell looks like a number, it is stored as text.

You can also use =ISTEXT(A2) as the reverse check. Other clues: text numbers often align left by default while real numbers align right, and a small green triangle may appear in the corner of the cell — but alignment and triangles are clues, not proof. ISNUMBER is the test.

What type of text number is it?

What it looks like	Most likely cause	Fix to try first
Plain digits, e.g. 1234	Imported or pasted as text, or entered after the cell was formatted as Text	Text to Columns (below)
Digits with leading/trailing spaces	Spaces from an import or copy-paste	Clean spaces first, then convert (see below)
Digits with a non-breaking space (CHAR 160)	Web or ERP copy-paste	SUBSTITUTE for CHAR(160), then convert — TRIM alone does not remove it
Digits with a currency symbol or thousands separators	Formatted text, not a formatted number	Remove the symbol/separators in a helper column, then convert; check the result
A date that looks like text, e.g. 01/02/2026	Date stored as text, or a DMY/MDY mismatch	See date formats — do not treat it as a plain number
A formula returning text, e.g. a formula that returns `""` or a quoted number	The formula produces text by design	Fix the formula to return a number, or wrap it in VALUE() — see below

Fix 1: Text to Columns (for a whole column)

Select the column containing the text numbers.
Go to Data → Text to Columns.
Choose Delimited, click Next, click Next again.
Under “Column data format”, choose General.
Click Finish.

This is the most reliable bulk fix for plain text numbers. If it does nothing, the values almost certainly contain something extra — spaces, non-breaking spaces, or a symbol — so go to Fix 3 first, then try again.

Fix 2: VALUE() (for single cells or formulas)

For one cell, or inside a formula, use:

=VALUE(A2)

VALUE converts text that looks like a number into a real number. If VALUE returns an error, the text is not a plain number — it contains a space, a non-breaking space, a symbol, or a date pattern that needs a different fix. Do not wrap every problem in VALUE and hope; find the extra character first.

Fix 3: clean spaces and invisible characters first

For space-type problems, the standard cleaning combination is:

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

SUBSTITUTE(..., CHAR(160), " ") handles non-breaking spaces first — TRIM alone does not remove them.
CLEAN removes non-printing characters.
TRIM removes leading/trailing spaces and collapses internal runs of spaces to one.

After cleaning, convert the result with Text to Columns or VALUE, and check it with ISNUMBER. For pasted text (not yet in a spreadsheet), you can clean it first with the Data Cleaner.

After the fix: check before you trust it

Re-run =ISNUMBER() on a few converted cells. They should now return TRUE.
Check that SUM, SUMIF, and sorting now behave as expected on the converted range.
Check for side effects: a leading zero that mattered (for example, a product code) should stay as text — do not bulk-convert a column of codes just because it looks numeric.
If a value was a date stored as text, converting it as a number will give you a serial number, not a date. Use the date fix for those cells instead.

Related: if a formula returns #VALUE!, text numbers are one of the most common causes — run the ISNUMBER check on every input to that formula first.

← Back to home