Using Excel Text Function – Because Looks Do Matter

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:

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