VALUE and NUMBERVALUE functions in Excel

VALUE turns text that looks like a number into a real number.

A number stored as text is the most common hidden fault in spreadsheets. It looks like a number. It refuses to add up, will not sort properly and does not match in lookups. VALUE converts it into a real number.

Syntax

=VALUE(text)

=NUMBERVALUE(text, [decimal_separator], [group_separator])

Argument	What it is
`text`	Text that represents a number, a date or a time.
`decimal_separator, group_separator`	For NUMBERVALUE: the characters used for the decimal point and for grouping thousands in the text.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: Text as people really type it: stray spaces, mixed capitals, codes with dashes.

	A	B	C	D
1	Full name	Email	Code	Amount as text
2	asha verma	asha.verma@example.com	PB-10-1234	1,250
3	RAVI KUMAR	ravi@example.org	DL-08-0042	300
4	meena gill	meena.gill@example.com	MH-12-9876	45.50
5	Kabir Khan	kabir@example.net	RJ-14-0007	12 kg

=VALUE(D3)
Result: 300
"300" stored as text becomes the number 300.
=VALUE(D2)
Result: 1250
"1,250" with a comma is understood too.
=VALUE(D4)
Result: 45.5
Spaces around " 45.50 " are ignored.
=VALUE(D5)
Result: #VALUE!
"12 kg" is not a number. The result is #VALUE!.
=VALUE(SUBSTITUTE(D5," kg",""))
Result: 12
Remove the unit first, then convert.
=SUM(D2:D4)
Result: 0
SUM ignores numbers stored as text: the total is 0.
=SUMPRODUCT(VALUE(D2:D4))
Result: 1595.5
Converting each cell inside the formula gives the true total.
=VALUE(MID(C2,4,2))
Result: 10
Digits cut out of a code with MID are text. VALUE makes them a number.
=NUMBERVALUE("1.250,75",",",".")
Result: 1250.75
A number written the European way, with a comma for decimals. NUMBERVALUE is told which character is which.

How to recognise a number stored as text

It sits at the left of the cell. Real numbers sit at the right unless you have changed the alignment.
A small green triangle may appear in the corner of the cell.
=ISNUMBER(A2) returns FALSE.
SUM skips it, so totals are too low or zero.
It will not match a real number in VLOOKUP, MATCH or COUNTIF.

They arrive from downloads, exports from accounting and banking systems, web pages, and from cells that were formatted as Text before the numbers were typed.

What VALUE understands

VALUE reads text in any format that Excel itself would accept if you typed it into a cell: plain digits, thousands separators, a currency sign of your region, a percent sign, a date or a time. Spaces at the ends are ignored.

It fails with #VALUE! on anything else: letters, units, a second decimal point. Remove those first with SUBSTITUTE, as the fifth example does.

Quicker ways to convert

VALUE is a formula. For data that only needs fixing once, these are faster.

The warning sign. Select the cells, click the yellow sign beside them and choose Convert to Number.
Paste Special. Type 1 in a spare cell and copy it. Select the text numbers, choose Paste Special, then Multiply. Each value is multiplied by one and becomes a number.
Text to Columns. Select the column, open Text to Columns on the Data tab and click Finish.

Inside a formula, arithmetic also converts: A2+0, A2*1 and --A2 (two minus signs) all do the job. VALUE has the advantage of saying plainly what is intended.

After cutting text

LEFT, RIGHT and MID always return text. If you cut the digits out of a code and want to use them as a number, VALUE is the natural last step.

The other direction

To turn a number into text in a particular format, use TEXT. The two are opposites: TEXT is for display, VALUE is for calculation.

Numbers from another country

In much of Europe, a comma marks the decimals and a dot or a space groups the thousands, so "1.250,75" means one thousand two hundred and fifty point seven five. VALUE reads text according to the regional settings of the computer and would misread it on an Indian or American system. NUMBERVALUE lets you name the two separators, so the conversion is right on any computer.

Dates stored as text

VALUE also converts a date written as text into the date's serial number, provided the text is in a form your regional settings recognise. A day-month-year text on a system that expects month-day-year gives an error or, worse, a wrong date. For imported dates, Text to Columns, where you can state the order of day, month and year, is safer.

Common mistakes

Converting codes that should stay as text. PIN codes, phone numbers and account numbers are not for calculating. As numbers they lose leading zeros, and long ones lose their last digits beyond the fifteenth.
Leaving a unit or a letter in the text.
A non-breaking space, which VALUE does not ignore. See TRIM.

In Google Sheets

VALUE exists in Google Sheets and behaves the same way. Sheets is more forgiving in general and often treats numeric text as a number in arithmetic by itself.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.