Using Excel Text Function – Because Looks Do Matter

Posted by Colleen Ludgate on Sunday, Feb 11th, 2018
Category : Microsoft Excel, Resources

Have you ever wanted to show a value in Excel, formatted exactly how you want to display it, with the value sourced from another cell?

The easy solution, of course, is to use the Format Cells dialog (available via the context menu) to change the format of the cell’s display.

Problem is, this only works if the display always stay the same. However, it is not applicable in two cases:

  1. You want to change the display of the cell at run time (for example, you want to display large values without the fraction)
  2. You want to display the contents of a different cell along with additional text (for example, to display “$2.12 discount” instead of “$2.12″)

If you have one of these cases, you’ll need to use the TEXT function.

This function takes two parameters.

  1. First is either a numeric value or a cell reference (of which the numeric value will be used).
  2. Second is the format you wish to use.

You can then combine the return value with another string, so you can easily use it to add additional text.

For example:

=TEXT("2.12", "$#.00") & " discount"

Will result in the cell displaying $2.12 discount

Now, the interesting thing about the TEXT function is the second parameter: the format.

It’s a string that defines the format that TEXT will use to, well, format the value which was passed as the first parameter.


Here are some ways that the format can be used:

  • To display fractions with specific number of digits, use ‘0’For example, the format “0.00” will always display the two significant fraction digits (even .00 if there’s no fraction)
  • To keep the decimal separator in the same place for cells in a column, use ‘?’For example, the format “??.???” will display “12345.789” and “12345.7” so the decimal period align.
  • To set the maximum amount of significant decimal digits, use ‘#’For example, the format “0.##” will display AT MOST two significant fraction digits: “12” will be kept as is, as will “2.31” or “8.9”, but “123.456” will be displayed as “123.46”
  • To format a date as a full US date, use “dddd dd mmmm, yyyy”For example, the 3/7/2010 be displayed as “Sunday 07 March, 2010″
  • You can even add some symbols and spaces to the numberFor example, “~# !” will display 12 as “~12 !”

Summary

The TEXT function’s format is very flexible, and can be used to format numbers in a lot of different ways, from decimals to date/time values, monetary values, percentages and scientific notation, and even be used to add various characters to the output.

The full documentation for the TEXT function is available here

Latest From Our Blog