Excel VALUE Function
last modified April 4, 2025
The VALUE function converts text that represents a number into a
numeric value. This tutorial provides a comprehensive guide to using the
VALUE function with detailed examples. You'll learn basic syntax,
practical applications, and common scenarios where VALUE is essential for data
conversion in Excel.
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.
| 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.
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 can then be used in mathematical calculations. Note the quotes 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" | |
| "25.75" | |
| "$1,000" | |
| =VALUE(A1) |
The table shows a simple spreadsheet with text values in column A and a
VALUE formula in cell B4 that converts the text in A1 to a number.
=VALUE(A1)
This formula converts the text "100" in cell A1 to the numeric value 100. The result can then be used in calculations. Note that VALUE cannot convert text with currency symbols or commas by default.
VALUE with Currency Symbols
This example shows how to handle text strings containing currency symbols and other non-numeric characters.
| A | B |
|---|---|
| "$125.99" | |
| "€250" | |
| "¥1,000" | |
| =VALUE(SUBSTITUTE(SUBSTITUTE(A1,"$",""),",","")) |
The table demonstrates how to combine VALUE with other functions to handle currency symbols and thousands separators. The nested SUBSTITUTE functions remove these characters before conversion.
=VALUE(SUBSTITUTE(SUBSTITUTE(A1,"$",""),",",""))
This formula first removes the dollar sign and then commas before converting the text to a number. The result for "$1,000" would be 1000. This technique can be adapted for different currency symbols.
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" | |
| "15-Jan-2023" | |
| "2023-01-15" | |
| =VALUE(A1) |
The table shows different text representations of dates in column A. The VALUE function in B4 converts the text date in A1 to Excel's date serial number.
=VALUE("01/15/2023")
This formula converts the text date "01/15/2023" to Excel's date serial number (44927 for this date). You can then format the result as a date. Note that date format recognition depends on your system's regional settings.
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 |
|---|---|
| "12:30 PM" | |
| "18:45" | |
| "9:15:30 AM" | |
| =VALUE(A1) |
The table demonstrates different text time formats in column A. The VALUE function in B4 converts the text time in A1 to Excel's time serial number.
=VALUE("12:30 PM")
This formula converts the text time "12:30 PM" to Excel's time value (0.520833, representing 12:30 PM as a fraction of a 24-hour day). The result can be formatted as time for display.
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.
The VALUE function is essential for converting text to numbers in
Excel. From simple number strings to complex date and time formats, VALUE
helps prepare data for calculations. Remember that VALUE cannot directly
handle currency symbols or thousands separators without preprocessing.
Mastering VALUE and its combinations with other functions will significantly
improve your data cleaning capabilities.
Author
List all Excel Formulas.