How to tell that numbers are stored as text
Look for any of these signs:
- Numbers aligned to the left of the cell instead of the right.
- A small green triangle in the corner of the cell, with the warning “Number Stored as Text”.
=SUM(D2:D100)returns 0, or less than you expect.=ISTEXT(D2)returnsTRUE.=COUNT(D:D)(numbers only) is smaller than=COUNTA(D:D)(everything).
| Row | A | B | C |
|---|---|---|---|
| 1 | Stored as | Looks like | SUM sees |
| 2 | Number | 1,200.00 | 1200 |
| 3 | Text | $1,200.00 | ignored |
| 4 | Text | 1 200,00 | ignored |
| 5 | Text | (300) | ignored |
Method 1: Convert to Number
For plain numbers with the green triangle, select the cells (you can select a whole column), click the yellow warning icon that appears and choose Convert to Number. It’s the quickest fix, but it only works on values Excel already recognises as numbers.
Method 2: Text to Columns
This converts an entire column in one go, and also handles trailing minus signs:
- Select the column.
- Choose Data → Text to Columns.
- Click Finish — or click Next twice, then Advanced, to set the decimal and thousands separators and tick Trailing minus for negative numbers.
Method 3: Paste Special → Multiply
- Type
1in an empty cell and copy it. - Select the text numbers.
- Choose Home → Paste → Paste Special, select Multiply, click OK.
Multiplying by 1 forces Excel to treat each value as a number. Delete the 1 afterwards.
Method 4: the VALUE function
In a helper column:
=VALUE(D2)VALUE understands thousands separators, percentages (12% becomes 0.12) and negatives in parentheses ((300) becomes -300) in your system’s number format. Copy the results and Paste Special → Values over the original column.
Method 5: NUMBERVALUE for other number formats
Data from another country may use a decimal comma and dots or spaces as thousands separators: 1.234,56 or 1 234,56. NUMBERVALUE lets you say which is which, whatever your own settings:
=NUMBERVALUE(D2, ",", ".")The second argument is the decimal separator in the text and the third is the thousands separator.
Method 6: remove symbols first with SUBSTITUTE
When values include currency symbols, codes or odd spaces, strip them before converting:
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2, "$", ""), ",", ""), CHAR(160), ""))This removes dollar signs, commas and non-breaking spaces (character 160, common in data copied from the web) and then converts the result. Add more SUBSTITUTE layers for other symbols, such as "R" or " USD".
Fixing dates stored as text
Text dates cause the same problems: they don’t sort chronologically, can’t be grouped by month, and break date formulas. Check with =ISNUMBER(B2) — real dates are numbers underneath, so text dates return FALSE.
If the text matches your system’s date order, DATEVALUE converts it:
=DATEVALUE(B2)Format the result as a date (Ctrl+1 → Date).
If the order differs — for example 14/03/2026 (day first) on a computer set to month first — use Data → Text to Columns, click Next twice, choose Date with the order the text uses (DMY), and click Finish. Or build the date explicitly from its parts:
=DATE(RIGHT(B2, 4), MID(B2, 4, 2), LEFT(B2, 2))That formula assumes two-digit days and months (05/03/2026). For a column that mixes formats, sort it first so similar values sit together, or use a tool that detects each format.
Ambiguous dates
03/04/2026 is 3 April in most of the world and March 4 in the US. Before converting, find a value in the column where the first number is greater than 12 — that tells you the column is day-first.
Keep identifiers as text
Not every number-looking value should be converted. Product codes, account numbers, ZIP and postal codes and phone numbers are identifiers: you never add them up, and converting them strips leading zeros (00123 becomes 123). Keep those columns as text — format the column as Text before pasting data into it.
In Google Sheets
Select the cells and choose Format → Number → Number. If values stay left-aligned, use =VALUE(A2) in a helper column, or Data → Split text to columns for whole columns. For text dates, =DATEVALUE(A2) works when the text matches the spreadsheet’s locale (File → Settings → Locale).
The fast way
MindySheets detects numbers stored as text in every column — including currency symbols, thousands separators, negatives in brackets, percentages and decimal commas — and converts them with one click, leaving identifiers with leading zeros alone. It spots mixed date formats too, works out whether a column is day-first or month-first, and rewrites every date the same way. Each change is listed in the download so you can check it.
Frequently asked questions
Why are my numbers stored as text in Excel?
Usually because they came from another system — a CSV export, a web page or a PDF — with symbols, spaces or an apostrophe in front, or because the column was formatted as Text before the numbers were typed.
How do I convert a whole column of text to numbers quickly?
Select the column, choose Data → Text to Columns and click Finish. Excel re-reads every cell and turns anything that looks like a number into one.
Why does VALUE return #VALUE!?
The text contains something VALUE can't read, such as a non-breaking space, a currency symbol it doesn't recognise, or a decimal comma when your system uses a decimal point. Remove those characters with SUBSTITUTE, or use NUMBERVALUE with the right separators.
Should I convert product codes and phone numbers to numbers?
No. Identifiers with leading zeros, like 00123 or 0821234567, should stay as text so the zeros aren't lost. Only convert values you'll add up or compare.