How to Convert Text to Number in Excel
Convert text to numbers in Excel so imported amounts, IDs, dates, and totals calculate correctly instead of looking right but behaving wrong.
Numbers stored as text are one of the most common reasons Excel totals, lookups, and filters behave strangely. The cells may look numeric, but Excel treats them like labels instead of values.
This usually happens after copying from a bank export, CSV, accounting system, CRM, or web report.
Quick fix with the warning icon
If Excel shows a small warning triangle, select the affected cells, click the warning icon, and choose Convert to Number.
This is the fastest fix when Excel has already detected the issue.
Multiply by 1
If the warning icon does not appear, put 1 in an empty cell, copy it, select the text-number range, then choose Paste Special -> Multiply.
Excel multiplies each selected value by 1, which converts many text numbers into real numbers without changing the displayed amount.
Use this carefully in an expense tracker or small business budget because blank cells, labels, and mixed fields should not be converted blindly.
Use VALUE in a helper column
For a visible formula approach, use VALUE.
=VALUE(A2)This converts a text number in A2 into a numeric value. After checking the results, copy the helper column and paste values over the original column if needed.
Remove hidden characters
Some imports include spaces, currency symbols, commas, nonbreaking spaces, or other hidden characters. Clean those first:
=VALUE(SUBSTITUTE(TRIM(A2),"$",""))If commas are part of the issue, remove them too:
=VALUE(SUBSTITUTE(SUBSTITUTE(TRIM(A2),"$",""),",",""))Common symptoms
| Symptom | Likely cause | Fix |
|---|---|---|
| SUM ignores values | Amounts are stored as text | Convert the amount column |
| Sort order looks wrong | Text sorts alphabetically | Convert numbers before sorting |
| Lookup misses matches | One side is text, one side is numeric | Normalize both keys |
| Green triangles appear | Excel detected text numbers | Use Convert to Number |
TIP
The Griddy way
Imported numbers can look fine until the review meeting, when totals or pivots do not match.
"Find columns where numbers are stored as text, convert amount fields safely, and leave invoice IDs and account codes alone"
Griddy can inspect the sheet, separate true amounts from identifier fields, and fix the columns that should actually calculate.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.
Use this on real templates
Fix imported numbers before totals drift
Finance templates are only reliable when amount, date, and quantity fields have the right data type before formulas and summaries run.
Expense Tracker
Log every expense, track receipts, and generate category summaries. Free template for personal or business use.
Open templateFinanceSmall Business Budget
Plan revenue, direct costs, overhead, and EBITDA in one compact operating budget. Keep H1 totals, margin, and owner notes visible without building a giant finance model.
Open templateFinanceInvoice Template
Professional invoice template with automatic subtotal, tax, and total calculations. Customise with your logo and send in minutes.
Open template