ElyxAI

Excel Convert Text to Number Function: VALUE

The Excel function that converts text to a number is VALUE: with the text in A2, use =VALUE(A2). When that text uses a different decimal mark, use =NUMBERVALUE(A2,",",".") in Excel 2013 or later.

ThomasCoget
18 min
Non classé
Excel Convert Text to Number Function: VALUE

The VALUE function

VALUE is the Excel function that converts text to a number. If A2 contains the text 1234, =VALUE(A2) returns the number 1234. The text has to be a number, date, or time that your Excel locale already recognizes, including a currency symbol or percent sign that matches that locale. Anything else returns #VALUE!.

SUM skips text that only looks like a number, which is why the conversion matters. Arguments and error cases are on the VALUE function page. This page stays on which function to use.

NUMBERVALUE when the decimal mark differs

VALUE uses the separators on your computer. Text written as 1.234,56 fails on an Excel set to a period decimal. NUMBERVALUE, in Excel 2013 and later, lets you name them: =NUMBERVALUE(A2,",",".") reads the comma as the decimal and the period as the thousands separator, and returns 1234.56.

The argument list is on the NUMBERVALUE function page.

Methods that are not a function

A green triangle and the warning Number Stored as Text are fixed in place with Convert to Number. Data, Text to Columns does the same without a formula. Those steps are on how to convert text to numbers. For plain digits only, =A2*1 also returns a number, and it returns #VALUE! when the text includes a currency symbol that VALUE would accept.

Direct answers

What Excel function converts text to a number?

VALUE. With the text in A2, use =VALUE(A2). It returns a real number that SUM can add. SUM skips text that only looks like a number.

What is the formula to convert text to a number in Excel?

=VALUE(A2). If the text uses a decimal mark your Excel does not use, use =NUMBERVALUE(A2,",",".") in Excel 2013 or later.

When should you use NUMBERVALUE instead of VALUE?

Use NUMBERVALUE when the text uses different separators from your Excel, for example 1.234,56 on a computer set to a period decimal. VALUE only accepts formats your locale already recognizes.

Why does converting text to a number show #VALUE!?

The text is not a number, date, or time Excel recognizes. A letter, a second decimal mark, or the wrong separator causes #VALUE!. For a separator mismatch, use NUMBERVALUE and name the separators.

Ask Elyx in Excel: "Convert these text values to numbers with VALUE." Elyx does it in the workbook. 7-day trial, no card.

Start the 7-day trial