VALUE

Returns a number, given a text representation.

Syntax:

VALUE(text)

converts text to a number. text may represent: a number, including fractions, exponential notation and (locale dependent) decimal point and thousands separator; a percentage; or a date or time (the datetime serial number is returned).

Example:

VALUE("12")

returns 12 as a number.

VALUE("1,234.56")

returns 1234.56 if your locale recognises the thousands separator ',' and decimal point '.'.

VALUE("1 1/2")

returns 1.5 (fraction).

VALUE("1E3")

returns 1000 (exponential notation).

VALUE("50%")

returns 0.5 (percentage).

VALUE("2009-04-03")

returns the datetime serial number for 3rd April 2009.

Application:

Imagine you're a data analyst for an e-commerce company, and you've just received a sales report in a text file. When you import this data, you notice that the "Sales" column, which should contain numbers, is being treated as text. This is because some of the cells have a currency symbol ($) or other characters that are interpreted as text.

When you try to calculate the total sales using SUM, you get an incorrect result (usually 0) because you can't sum text values.

Table Example:

	Sales Data (as Text)	Calculation with VALUE
	A	B
1	$1,250.00	$1,250.00
2	$2,100.50	$2,100.50
3	$950.25	$950.25
4	$1,500.00	$1,500.00
5	Total (Incorrect)	Total (Correct)
6	0	$5,800.75

In this example:

Column A is the raw sales data, which is seen as text.
Column B uses the VALUE function to convert the text in Column A into a number. For example, the formula in cell B2 is VALUE(A2).
When you try to use SUM on the text values in Column A, the result is incorrect.
When you use SUM on the converted number values in Column B, the result is correct, allowing you to perform calculations and further analysis.

This is a common scenario, and the VALUE function is an essential tool for cleaning and preparing data for analysis.