Microsoft Excel comes with a TEXT function which allows you to change the number format of a cell. Its syntax is "TEXT(value, format)," where "value" can be a formula, name or cell reference. The TEXT function supports a wide variety of text formats. It allows you to create dynamic strings and sentences for better data presentation. What the TEXT function cannot do, however, is change colors or add borders. This is possible only through the use of conditional formatting.
Open your workbook and highlight the cell that you'd like to edit.
Go to the Formula bar and enter the formula below. Replace "value" with a number, a cell reference or a formula. To replace it with a cell reference, highlight "value" in your Formula bar, and click the cell to which you'd like the function to point.
Replace "format" in your formula with the number format you'd like to apply to your data (see Resources for a list of acceptable format strings). Be sure to enclose it in quotation marks.
More Ways to Use the TEXT Function
Prefix your values with appropriate currency symbols. To do this, enter your preferred currency symbol at the beginning of the format string. For example, the following formula outputs "$25.00."
Enclose a comma sign within pound symbols to display the thousands separator. The following example outputs "12,200,000."
Show decimal places and round off values using the "0" (zero) placeholder. The following examples will output "25.567," "25.6" and "26" respectively.
Convert long numbers to shorthand format by using the comma symbol. The following examples will output "12.5k" and "12.6m" respectively.
Show fractions and pound signs using the forward slash ("/"). For example, the following formula will show "2 3/5" and "13/5" respectively.
=TEXT(2.6, "# #/#")
Tips & Warnings
- Precede reserved letters and symbols with the backslash character ("\") in order to explicitly show them.
- Combine the TEXT function with words and strings to create dynamic sentences. Assuming that "A1" contains your date of birth, the following formula will tell you how old you are:
- ="You are now " & TEXT(TODAY()-A1, "y") & " years old"
- Photo Credit Jupiterimages/Photos.com/Getty Images
How to Use Commas
Although they are meant to be helpful, commas can be a major pain. They aren't as obvious as periods, but luckily, they...
How Can I Print My Formulas in Excel?
Microsoft Excel is a popular spreadsheet program, and it allows the entry of data or formulas into each cell. A formula in...