Tuesday, October 16, 2012

Test the TEXT function!

We will learn something interesting today about a lesser used text function, which is called “TEXT” itself!! Can you ask for an easier name to remember a function? 

Ok, let us get on with the brilliant features available with this function.

=TEXT(value, format)

Where value is the value to convert to text, and format is the format to display the results in.

Format can take one of the following modes – number, date, time, currency, percentage, scientific notation and finally the default text format too.

This function is very rich and powerful with all these different formats and notations, and it would be easier to understand the use of this with some practical examples, than with boring text. 

Look at the following image to understand the concept better.


Practical tips:

In a real life scenario, you might come across a situation where you get some values in different formats, for instance, the date might get populated as a text and come to you from a database. In such situations, this function can be very handy as you can use a combination of standard set of DATE functions, the LEFT, MID and RIGHT functions to extract the date strings and use the TEXT function to output it in the desired format. 

Similar applications of this function is virtually unlimited and the scope would be as large as your ability to imagine solutions out of this wonderful multipurpose utility function.

Download link:

The example shown above can be downloaded and viewed as well - the sample workbook is stored here for your ready reference - http://sdrv.ms/WiHRNq 

My upcoming book on Excel contains many more such tricks and practical insights into getting Excel work for you. Please take a moment to visit my earlier post to understand details about my new book, and make use of the special offer available now to order it at a discount.