Excel VALUE function to convert text to numbers

This tutorial explains how Excel's VALUE function converts text strings into numerical values. While Excel usually handles number-as-text automatically, data in unrecognizable formats can remain as text, preventing calculations. The VALUE function provides a solution.

• Excel VALUE function syntax
• Using the VALUE function: formula examples
  • Text to number conversion
  • Extracting numbers from text strings
  • Text to date/time conversion
• Troubleshooting the #VALUE! error

Excel VALUE Function

The VALUE function converts text to numbers, recognizing numeric strings, dates, and times. Its syntax is simple:

VALUE(text)

where text is a text string (in quotes) or a cell reference containing the text. Introduced in Excel 2007, it's available in all later versions. For example, =VALUE(A2) converts the text in cell A2 to a number. Note the difference in alignment: text is left-aligned, numbers are right-aligned.

Using the VALUE Function: Examples

While Excel often automatically converts text to numbers, explicit conversion is sometimes necessary.

Text to Number Conversion

The VALUE function handles various text formats:

Formula	Result	Explanation
=VALUE("$10,000")	10000	Converts the text string to its numerical equivalent.
=VALUE("12:00")	0.5	Returns the decimal equivalent of 12 PM (Excel's internal representation).
=VALUE("5:30") VALUE("00:30")	0.25	Decimal equivalent of 6 AM (5:30 0:30 = 6:00).

Extracting Numbers from Text Strings

Functions like LEFT, RIGHT, and MID extract characters, but their output is always text—even when extracting numbers. This can hinder calculations. Wrap these functions in VALUE for numerical results.

For example:

• Extract the first two characters as a number: =VALUE(LEFT(A2,2))
• Extract two characters starting at the 10th position: =VALUE(MID(A3,10,2))
• Extract the last two characters as a number: =VALUE(RIGHT(A4,2))

(Shows the problem before VALUE) (Shows the solution with VALUE)

Text to Date/Time Conversion

VALUE converts date/time text strings to serial numbers (integer for date, decimal for time). Format the cell as a date or time to display the result correctly. Alternatively, use DATEVALUE or TIMEVALUE for more control.

Troubleshooting the #VALUE! Error

The #VALUE! error occurs if the input text is in an unrecognized format. More complex formulas (as shown in How to extract number from string in Excel) might be needed in such cases.

This tutorial provides a basic understanding of the Excel VALUE function. A practice workbook is available for download: Excel VALUE formula - examples (.xlsx file)

The above is the detailed content of Excel VALUE function to convert text to numbers. For more information, please follow other related articles on the PHP Chinese website!