Convert Text That Looks Like a Number

Excel Formulas › Text

All versionsVALUE

Numbers imported as text won’t sum and sort wrong — often shown by a little green triangle. VALUE, or a quick math nudge like multiplying by 1, converts them back to real numbers.

Quick formula: convert text "1,234" to a real number:
=VALUE(A2) // or =A2 * 1
VALUE parses a numeric string; multiplying by 1 (or adding 0, or --) forces the same coercion.

Functions used (tap for the full reference guide):

VALUE

The example

Text “1,234” becomes the number 1234.

AB
1TextNumber
2"1,234"1234
3" 56 "56

The formula

The formula:

=VALUE(A2) // or =A2*1, =A2+0, =--A2

How it works

How it works:

  1. VALUE(text) parses a string that looks like a number into a real number.
  2. A math nudge does the same: =A2*1, =A2+0, or the double-unary =--A2 all coerce text to a number.
  3. Wrap in TRIM first if the text has stray spaces: =VALUE(TRIM(A2)).
  4. To convert a whole column at once, Paste Special → Multiply by 1, or use Text to Columns.

The green triangle in a cell’s corner flags “number stored as text.” Select the range, click the warning icon, and choose Convert to Number for a no-formula fix. For currency or locale formats, VALUE respects your regional separators; for fixed formats, NUMBERVALUE lets you specify them explicitly.

Try it: interactive demo

Live demo

Text that looks numeric.

Number: · Can sum?

Variations

Math nudge

Quickest:

=A2 * 1

Trim then convert

Strip spaces:

=VALUE(TRIM(A2))

Specify format

Custom separators:

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

Pitfalls & errors

Non-numeric text errors. VALUE returns #VALUE! if the string isn’t a recognizable number — clean it first.

Hidden spaces & characters. Imported text may carry CHAR(160) (non-breaking space); wrap in SUBSTITUTE/TRIM/CLEAN before VALUE.

Locale matters. Decimal and thousands separators follow your region; use NUMBERVALUE to set them explicitly.

Practice workbook

📊
Download the free Convert Text That Looks Like a Number practice workbook
A text-to-number sheet with the math-nudge, trim, and NUMBERVALUE variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I convert text to a number in Excel?
Use =VALUE(A2), or nudge with math: =A2*1, =A2+0, or =--A2. All coerce numeric-looking text into a real number.
Why won't my imported numbers sum?
They're stored as text (often flagged by a green triangle). Convert with VALUE or a math nudge, or use the cell warning's Convert to Number option.
VALUE returns #VALUE! — what's wrong?
The text isn't a recognizable number, or it has hidden characters like non-breaking spaces (CHAR(160)). Clean it with TRIM/SUBSTITUTE/CLEAN first.

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 · Clean text · Extract numbers

Function references: VALUE