How to Use the Text() Function In Excel

• Tweet

• Tweet

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.

Excel’s TEXT function allows users to type in a function that returns a value converted to text. For instance, you can use it to change the way a date is displayed or change a number value to currency. You can also create a sentence that includes the values. Problem is, this only works if the display always stay the same. However, it is not applicable in two cases:

Using the Text() Function In Excel

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 number

For 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:
http://office.microsoft.com/en-us/excel-help/text-function-HP010342952.aspx

About the author

Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to Excel conversion software.
For more Excel tips from Yoav, join him on Facebook or Twitter

Post on – www.geekblogger.org | Subscribe our Feeds @ http://feeds.feedburner.com/geekbloggerorg