Convert Text That Looks Like Numbers

Excel Formulas › Text

All versionsVALUE

Numbers stored as text won’t sum, sort, or chart — they sit left-aligned with a little green triangle. VALUE (or a quick ×1) converts them back to real numbers Excel can compute with.

Quick formula: to convert text-number in A2:
=VALUE(A2)
Returns a real number. A shortcut that often works just as well: =A2 * 1 or =A2 + 0 forces numeric conversion.

Functions used (tap for the full reference guide):

VALUENUMBERVALUE

The example

Text amounts converted so they can be summed.

AB
1TextNumber
2'1,2501250
3'4242
4SUM1292

The formula

Force the text into a real number:

=VALUE(A2) // or =A2*1 // "1,250" → 1250 (a real number)

How it works

Excel needs a numeric type, not numeric-looking text:

  1. VALUE(A2) parses the text and returns a number, respecting your regional thousands and decimal separators.
  2. A math operation that doesn’t change the value — A2*1, A2+0, or --A2 — also coerces text to a number.
  3. For text using a different region’s separators, NUMBERVALUE(A2, ".", ",") lets you specify the decimal and group characters explicitly.
  4. No formula? Select the range, click the warning triangle, and choose Convert to Number — or use Text-to-Columns and finish on a numeric column.

Fastest bulk fix: copy any empty cell, select the text-numbers, then Paste Special → Add. Adding zero converts the whole range to real numbers in place — no helper column.

Try it: interactive demo

Live demo

Type a number with commas; see the converted value.

Number:   Can do math:

Variations

Math coercion

No VALUE needed:

=A2 * 1

Double-negative

A common pro shortcut:

=--A2

Foreign separators

Specify decimal/group chars:

=NUMBERVALUE(A2, ".", ",")

Pitfalls & errors

VALUE errors on real text. If the cell isn’t actually a number (“N/A”, “TBD”), VALUE returns #VALUE!. Wrap with IFERROR if mixed content is possible.

Leading apostrophes hide. A cell starting with ' is forced to text; VALUE or Convert to Number removes the effect.

Regional separators matter. “1.250” means 1250 in some locales and 1.25 in others. Use NUMBERVALUE to be explicit.

Practice workbook

📊
Download the free Convert Text That Looks Like Numbers practice workbook
A text-to-number sheet with VALUE, the math-coercion, double-negative, and NUMBERVALUE variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I convert text to numbers in Excel?
Use =VALUE(A2), or coerce with math like =A2*1 or =--A2. For a no-formula fix, select the range and choose Convert to Number from the warning triangle.
How do I convert a whole column of text-numbers at once?
Copy an empty cell, select the range, then Paste Special → Add. Adding zero converts everything to real numbers in place.
Why does VALUE give a #VALUE! error?
The cell contains non-numeric text. VALUE only works on numeric-looking text; wrap it in IFERROR if some cells are genuinely text.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Convert text to date · Extract numbers · Remove extra spaces

Function references: VALUE · NUMBERVALUE