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:
If you have one of these cases, you’ll need to use the TEXT function.
This function takes two parameters.
You can then combine the return value with another string, so you can easily use it to add additional text.
=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.
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 number
For example, “~# !” will display 12 as “~12 !”
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