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.

=VALUE(MID(SUBSTITUTE(C52,” “,” “),SEARCH(“???%”,SUBSTITUTE(C52,” “,” “)),4))