Value in Excel

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 recognized formats.                 

         

Content Data

Text Containing A Number	Value
Demo Calculation $200	200	=VALUE(MID(C4,SEARCH("$",C4),99))

Explanations   

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.