VALUE (Estimates variance based on a sample)

What Does It Do?

This function converts a piece of text which resembles a number into an actual value.

If the number in the middle of a long piece of text it will have to be extracted using other text functions such as =SEARCH(), =MID(), =FIND(), =SUBSTITUTE, =LEFT() or =RIGHT().

Syntax

=VALUE(TextToConvert)

Formatting

No special formatting is needed.

The result will be shown as a value, based upon the original text.

If the £ sign is included in the text it will be ignored.

If the % sign is included in the text, the result will be a decimal fraction which can then be formatted as a percentage.

If the original text format appears as a time hh:mm the result will be a time.

The same will be true for other recognised formats.

Explanation of formula shown above

To extract the values from the following text is complicated!

The actual percentage value is of variable length, it can be either one, two or three digits long.

The only way to identify the value is the fact it always ends with the % sign.

There is no way to identify the beginning of the value, other than it is preceded by a space.

The main problem is calculating the length of the value to extract.

If the extraction assumes the maximum length of three digits and the % sign, errors will occur when the percentage is only one digit long, as alphabetic characters will be included.

To get around the problem the =SUBSTITUTE() function was used to increase the size of the spaces in the text.

Now when the extraction takes place any unnecessary characters will be spaces which are ignored by the =VALUE() function.