Excel VALUE Function

VALUE converts text that looks like a number, date, or time into a real number you can use in calculations. If the text doesn't match a format Excel recognizes, VALUE returns a #VALUE! error instead of guessing at what you meant. That's the one thing to remember before you wrap it around anything else.

Syntax

=VALUE(text)

Parameter	Required	Description
`text`	Yes	The text you want to convert into a number. Can be a cell reference, a text string typed in quotes, or the result of another formula like MID or LEFT.

ℹ

A cell can display something that looks exactly like a number while Excel still stores it as text underneath, which is why SUM ignores it and VALUE exists to fix it.

Basic Example

You're importing a shipping report where order quantities show up as text instead of numbers, something that happens constantly with CSV exports from older systems. Cell A2 contains "150," but it's stored as text, left-aligned, not a real value.

=VALUE(A2)

// A2 = the text string "150" pulled from a CSV export

VALUE reads the digits in A2 and returns the number 150, exactly as if you'd typed it directly into the cell. Drop this formula into a helper column next to your imported data, then reference the helper column in SUM instead of the original text column.

How VALUE Works

What counts as a recognized format

VALUE accepts more than plain digits. It recognizes currency symbols, thousands separators, percentages, dates, and times, as long as the format matches how Excel would normally display that value on your system. "$1,240.50" converts to 1240.5. "45%" converts to 0.45. "3/15/2026" converts to the serial number Excel uses internally for that calendar date.

Why SUM sometimes ignores numbers that look fine

SUM and COUNT skip text values entirely, even when the text looks identical to a real number. Only arithmetic operators like +, -, and * force Excel to coerce text into a number automatically. VALUE performs that same coercion explicitly, so =VALUE(A2)+VALUE(B2) behaves the same as =A2+B2 when both cells hold text numbers, but the VALUE version is easier to spot later when someone else audits the workbook.

VALUE with dates and times returns serial numbers, not calendar dates

Feed VALUE a date string and it hands back Excel's underlying serial number, not a formatted date. You'll need to apply date formatting to the result cell yourself, or reach for DATEVALUE instead, which performs the identical conversion but is named for that specific case.

VALUE follows your regional settings

VALUE interprets decimal and thousands separators based on your system's locale. A workbook opened on a machine set to European regional settings reads "1.234,56" as one thousand two hundred thirty four point five six. Open that same text on a US-configured install and VALUE either misreads it or throws an error. NUMBERVALUE lets you specify the separators explicitly; VALUE does not.

Common Use Cases

Cleaning up imported financial data

A finance report exported from an accounting system stores every amount as text with a dollar sign, like "$4,850.00" in cell C2.

=VALUE(C2)   // returns 4850, ready for SUM or AVERAGE

Extracting a quantity from a product code

Product codes like "SKU-88214-24PK" bury the pack quantity in the middle of the string. Pull it out with MID, then convert the result with VALUE.

=VALUE(MID(A2,12,2))

// MID(A2,12,2) pulls the text "24" starting at character 12
// VALUE converts that extracted text into the number 24

For variable-length codes, TEXTSPLIT often replaces this pattern in Excel 365. Its output is still text though, so it still needs VALUE wrapped around it before you can do math with the result.

Converting text dates for calculations

A vendor invoice list stores due dates as text, like "March 15, 2026" in column D. To calculate days overdue, convert the text into a real date first.

=TODAY()-VALUE(D2)   // returns the number of days between March 15, 2026 and today

Fixing totals that silently exclude text numbers

A budget tracker imported from a legacy system has quantities stored as text in column E. SUM(E2:E50) returns a partial total, or zero, because SUM is quietly skipping every text cell.

=VALUE(E2)   // build this in a helper column, drag it down, then SUM the helper column instead of E2:E50

Handling Errors

VALUE returns #VALUE! when the text doesn't match a number, date, or time format it recognizes. This is the only error VALUE throws, and it fires immediately, not after some silent failure downstream.

Common causes of #VALUE!:

Leading or trailing spaces, including non-breaking spaces copied from a website or PDF
Text that includes letters or symbols VALUE doesn't recognize, like "Qty: 150" without extracting the number first
A decimal or thousands separator that doesn't match your regional settings
An empty string "" passed in place of real text

=IFERROR(VALUE(A2), "Check format")

✓

Imported text often carries invisible characters that break VALUE. Wrap the argument in TRIM and CLEAN first: =VALUE(TRIM(CLEAN(A2))) handles most copy-paste garbage from PDFs and web pages in one step.

Notes & Gotchas

What does the VALUE function do in Excel?

VALUE converts text that represents a number, date, or time into an actual numeric value Excel can use in calculations. It exists because a cell can display something that looks exactly like 150 while Excel still treats it as a text string, which breaks SUM, AVERAGE, and comparison operators until you convert it.

How do you use the VALUE function in Excel?

Wrap VALUE around the cell or text string you want converted: =VALUE(A2) or =VALUE("1,240.50"). Put the result in a new cell or a helper column rather than overwriting your original imported data, so you can trace back to the source if a conversion produces the wrong number.

Why does VALUE return a #VALUE! error?

VALUE throws #VALUE! whenever the text doesn't match a number, date, or time format it recognizes. Extra spaces, stray letters, and mismatched decimal separators are the three most common culprits, and none of them are visible just by looking at the cell.

What's the difference between VALUE and Excel's automatic text-to-number conversion?

Excel automatically converts text-formatted numbers when you use arithmetic operators like + or *, but functions like SUM and COUNT ignore text values without converting them first. VALUE forces that conversion explicitly, which matters any time you're building a formula around SUM, COUNTIF, or a comparison operator instead of plain addition.

How can VALUE be combined with text extraction functions to convert extracted text into numbers?

MID, LEFT, RIGHT, and TEXTSPLIT all return text, even when the substring they extract looks like a number. Wrap any of them in VALUE, as in =VALUE(MID(A2,12,2)), to turn the extracted text into a number you can sum, average, or compare numerically.

Does VALUE work with dates and times?

Yes, but it returns Excel's serial number for that date or time, not a formatted date. "3/15/2026" converts to 46096, Excel's internal day count, not a value that displays as a calendar date until you format the cell yourself.

What happens if the cell is blank or contains only spaces?

An empty cell returns 0. A cell containing only spaces, even a single space character, returns #VALUE! because a space isn't a recognized number format. This distinction trips people up when auditing why some blank-looking cells throw errors and others quietly return zero.

Related Functions

Function	Use this when...
`DATEVALUE`	You only need to convert text into a date, not a general number.
`TIMEVALUE`	You're converting text into a time value instead of a date or plain number.
`NUMBERVALUE`	Your text uses decimal or thousands separators that don't match your system's regional settings.
`TEXT`	You need the opposite conversion: turning a number into formatted text.