VALUE Function (Functions in Excel)

Text Containing A Number	Value
Annual turnover was £5000	#VALUE!	=VALUE(MID(C4,SEARCH("£",C4),99))
There was a 2% increase in sales.	0.02
There was a 50% increase in sales.	0.5
A 100% increase was achieved.	1
Only a 2% increase in sales.	2%
Approx 50% increase in sales.	50%
There was a 100% increase in sales.	100%	* See explanation below.
	=VALUE(MID(SUBSTITUTE(C11," ","    "),SEARCH("???%",SUBSTITUTE(C11," ","    ")),4))
The winning time was 1:30 seconds.	0.0625	=VALUE(MID(C14,SEARCH("??:??",C14),5))
The winning time was 1:30 seconds.	1:30	=VALUE(MID(C15,SEARCH("??:??",C15),5))
The winning time was 10:30 seconds.	10:30	=VALUE(MID(C16,SEARCH("??:??",C16),5))
The winning time was 0:30 seconds.	0:30	=VALUE(MID(C17,SEARCH("??:??",C17),5))

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.

There was a 2% increase in sales.	0.02
There was a 50% increase in sales.	0.5
There was a 100% increase in sales.	1
=VALUE(MID(SUBSTITUTE(C52," ","    "),SEARCH("???%",SUBSTITUTE(C52," ","    ")),4))

Related Posts by Categories