Excel VALUE Function
last modified October 5, 2026
The VALUE function converts text that represents a number into an
actual numeric value. It understands the constant number, date, and time formats
that Excel itself recognizes, so it can turn strings such as "123.45", "$1,000",
"50%", or "16:48:00" into numbers you can calculate with. This tutorial provides
a comprehensive guide to using the VALUE function with detailed
examples. You'll learn its syntax, which formats it recognizes, how locale
settings affect the result, and how it compares with NUMBERVALUE
and the double unary minus.
VALUE Function Basics
The VALUE function converts text strings that represent numbers
into actual numeric values. This is particularly useful when importing data
from other systems that may store numbers as text. The result is a plain number
with no number formatting attached.
| Component | Description |
|---|---|
| Function Name | VALUE |
| Syntax | =VALUE(text) |
| Arguments | text - The text to convert to a number |
| Return Value | Numeric value of the text string |
This table breaks down the essential components of the VALUE
function. It shows the function name, basic syntax format, argument
requirements, and return value characteristics.
| Argument | Meaning |
|---|---|
| text | The text to convert. It can be a literal string in quotation marks, a
cell reference, or an expression that returns text, such as
A1&"". The text must match a constant number, date, or time
format that Excel recognizes. |
VALUE takes exactly one argument. If the text cannot be matched to
a recognized format, the function returns the #VALUE! error. You
rarely need VALUE for data you type yourself, because Excel
converts text to numbers automatically when a calculation requires it; the
function exists mainly for compatibility with other spreadsheet programs.
Basic VALUE Example
This example demonstrates the simplest use of the VALUE function with a text string that represents a number.
=VALUE("123.45")
This formula converts the text string "123.45" into the numeric value 123.45. The result has no number formatting and can be used directly in mathematical calculations. Note the quotation marks around the text value.
VALUE with Cell References
A more practical use of VALUE involves converting text values
stored in cells. Here's an example with cell references.
| A | B |
|---|---|
| "100" | =VALUE(A1) |
| "25.75" | =VALUE(A2) |
| "$1,000" | =VALUE(A3) |
The table shows a simple spreadsheet with text values in column A and
VALUE formulas in column B. Each formula converts the text in the
cell to its left into a number.
=VALUE(A1)
This formula converts the text "100" in cell A1 to the numeric value 100. The same function also reads "$1,000" as 1000 when the dollar sign and comma match the separators of the current locale. How to deal with symbols and formats that the locale does not recognize is shown below.
Recognized Number Formats
VALUE accepts any constant number format that Excel understands.
That includes whole numbers, decimals, leading signs, scientific notation,
percentages, currency amounts, thousands separators, and accounting-style
negatives in parentheses. The table below shows the result of each format.
| Formula | Result |
|---|---|
| =VALUE("123") | 123 |
| =VALUE("1.5") | 1.5 |
| =VALUE("-42") | -42 |
| =VALUE("$1,234.56") | 1234.56 |
| =VALUE("50%") | 0.5 |
| =VALUE("1E2") | 100 |
| =VALUE("(100)") | -100 |
| =VALUE(" 10 ") | 10 |
Leading and trailing spaces are ignored, so the last formula returns 10. The dollar sign, the thousands comma, the percent sign, the parentheses, and the decimal point are all examples of formatting characters that Excel recognizes and removes while building the number. The currency symbol and the separators that are accepted depend on the regional settings of the computer, which is discussed in the locale section below.
Percent, Currency, and Accounting Numbers
Percentages, currency values, and accounting negatives each carry punctuation that VALUE knows how to interpret. The following formulas show how these characters affect the result.
=VALUE("32.5%") ' 0.325
=VALUE("$125.50") ' 125.5
=VALUE("(75.25)") ' -75.25
A trailing percent sign divides the number by 100, so "32.5%" becomes 0.325,
not 32.5. A leading currency symbol is dropped, so "$125.50" becomes 125.5.
Parentheses mark a negative amount in accounting notation, so "(75.25)" becomes
-75.25. When a value mixes several decorations, for example "$1,234.56", VALUE
removes each recognized character in turn. A symbol that the locale does not
recognize, such as a euro sign on a US computer, is not accepted and must be
stripped first with SUBSTITUTE.
VALUE with Dates
The VALUE function can convert text representations of dates into Excel date serial numbers. This example demonstrates this capability.
| A | B |
|---|---|
| "01/15/2023" | =VALUE(A1) |
| "15-Jan-2023" | =VALUE(A2) |
| "8/22/2023" | =VALUE(A3) |
The table shows different text representations of dates in column A. The VALUE formulas in column B convert each text date into Excel's date serial number.
=VALUE("01/15/2023") ' 44941
=VALUE("8/22/2023") ' 45160
In the 1900 date system, "01/15/2023" becomes the serial number 44941 and "8/22/2023" becomes 45160. Format the result as a date to display it as a date again. Whether a string such as "15-Jan-2023" or "2023-01-15" is recognized at all depends on the system's regional date settings, so always test date text on the machines that will open the workbook.
VALUE with Time Text
Similar to dates, VALUE can convert text representations of times into Excel time values. This example shows this application.
| A | B |
|---|---|
| "16:48:00" | =VALUE(A1) |
| "12:30 PM" | =VALUE(A2) |
| "18:45" | =VALUE(A3) |
The table demonstrates different text time formats in column A. Each VALUE formula converts the text time to Excel's time serial number, a fraction of a 24-hour day.
=VALUE("16:48:00") ' 0.7
=VALUE("12:30 PM") ' 0.520833
=VALUE("18:45") ' 0.78125
"16:48:00" is 16 hours and 48 minutes, which equals 0.7 of a day. "12:30 PM" is
0.520833 and "18:45" is 0.78125. You can subtract time values directly, so
=VALUE("16:48:00")-VALUE("12:00:00") returns 0.2, which is 4 hours
and 48 minutes. Format the result as a time to display it as a clock time.
VALUE and Locale Dependence
VALUE is locale-dependent. It uses the decimal separator, the group
separator, the currency symbol, and the date and time formats of the computer
that opens the workbook. The same text can therefore convert to different
numbers, or fail, on different machines.
| Locale | Decimal | Group | =VALUE("1.234,56") |
|---|---|---|---|
| United States | . | , | #VALUE! |
| Germany | , | . | 1234.56 |
In the US locale the period is the decimal separator and the comma is the group
separator, so the comma in "1.234,56" is invalid and the function returns
#VALUE!. In the German locale the roles are reversed, so the same
text converts to 1234.56. Use NUMBERVALUE with explicit separators
when the number format must be read the same way on every computer, and
DATEVALUE or TIMEVALUE when you want a locale-stable
date or time conversion.
VALUE vs NUMBERVALUE vs the Double Unary
Three common techniques turn text into a number. They differ in how much control and locale independence they provide.
| Feature | VALUE | NUMBERVALUE | --text |
|---|---|---|---|
| Separators | Taken from the current locale | You specify decimal and group separators | Taken from the current locale |
| Locale independence | No | Yes, when both separators are supplied | No |
| Dates and times | Supported | Not supported | Not reliable |
| Currency symbols | Accepted in the matching locale | Not supported, returns #VALUE! | Accepted in the matching locale |
| Typical use | Explicit, readable conversion | Fixed international formats | Short conversion inside a larger formula |
The double unary minus, --text, forces Excel to coerce text to a
number through the same arithmetic conversion used by =A1+0. It is
short and handy inside larger formulas, and it accepts ordinary numbers
including locale-formatted currency and percent text, but it is not a substitute
for VALUE when you need to convert recognized date and time
strings. NUMBERVALUE is the only one of the three that ignores the
locale entirely, so prefer it for imported data whose format you control.
VALUE with Error Handling
When VALUE encounters text it cannot convert to a number, it returns a #VALUE! error. This example shows how to handle such cases.
| A | B |
|---|---|
| "123" | |
| "ABC" | |
| "45.67" | |
| =IFERROR(VALUE(A2),"Invalid number") |
The table shows how to combine VALUE with IFERROR to handle conversion errors gracefully. The formula in B4 attempts to convert A2 but returns a custom message if conversion fails.
=IFERROR(VALUE(A2),"Invalid number")
This formula attempts to convert the text in A2 to a number. If successful, it returns the number. If not, it returns "Invalid number" instead of an error. This makes your spreadsheets more user-friendly.
VALUE Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| Text is not a recognized number, date, or time format | #VALUE! error | Clean the text or test it with IFERROR |
| Text contains a foreign or unexpected symbol | #VALUE! error | Remove the symbol with SUBSTITUTE before converting |
| Separators do not match the current locale | Wrong number or #VALUE! | Use NUMBERVALUE with explicit separators |
| Date or time text does not match the system format | #VALUE! error | Use DATEVALUE or TIMEVALUE with a known format |
| Text contains hidden or non-breaking spaces | #VALUE! error | Clean the text with TRIM or SUBSTITUTE before converting |
| Underlying value is already an error | The same error is passed through | VALUE cannot mask #N/A, #DIV/0!, or similar errors |
| Result shows the date serial instead of a date | A number such as 44941 | Format the cell as a date to display it correctly |
The VALUE function is essential for converting text to numbers in
Excel. From simple number strings to percent, currency, and accounting amounts,
and on to date and time text, VALUE prepares data for calculation
whenever Excel has not already done so on its own. Remember that it follows the
regional settings of the reader, so the same workbook can behave differently on
another computer; pass explicit separators through NUMBERVALUE when
consistency matters. By pairing VALUE with
SUBSTITUTE, DATEVALUE, and IFERROR, you
can build robust data-cleaning formulas that handle almost any text-based number
format.
Author
List all Excel Formulas.