Unleashing the Flexibility of Excel Text Formulas
Data collection is an important part of business. It is customer satisfaction survey results, employee performance assessments, market and industry research, lead generation analysis, or anything that depends on collecting, comparing and seeking relevant information.
Unfortunately, depending on the setup and your tools, raw data can be confusing and impossible to share unless everyone speaks the language. Have you ever opened a document from a colleague (or prepared a document) that looks like gibberish?
The answer: You need a form that is user-friendly and easy-to-read. A complicated spreadsheet might be what you need for analysis, but forget forcing your boss, fellow employees, or customers to understand it!
Example: You have a deadline to provide your results, yet the dates in your spreadsheet look more like laboratory results. “Easy-to-read” isn’t something that raw, unformatted Excel workbooks are known for.
Let’s suppose you’re designing a summary form that should say, “You have 4 tasks remaining, the first due by 3:00 PM,” where the “4” and the “3:00 PM” are drawn from constantly updated cells in the workbook.
The first half is easy, thanks to Excel’s use of “&” to join text from different sources. If cell D6 shows the number of tasks remaining, just type the formula
=“You have ”&D6&“ tasks remaining”
and Excel fills in the results of cell D6.
Try it out. Did you get a regular sentence?
NOTE: Don’t forget the spaces inside the quotation marks; without them, Excel mashes the words right up against the number 4 like this: You have4tasks remaining. (For more help with this method, see “CONCATENATE ### link”).
The second half of our summary report phrase is more difficult. Try a formula like the one above, and Excel might display “the first due by 41835.625.” This probably isn’t what your boss intended when she requested a customer-friendly and easy-to-read form.
Why does this happen in text formulas, and how can you fix it?
Excel Separates a Number’s Value from its Format
When you type the number 4 into your workbook, Excel stores the value, and that value does not change whether you choose to display it as 4.00 or 4.0000 or $4.00 or 4e00. To see the unformatted number, go to the Home ribbon and apply the General format to the cell. This displays the raw value with no specific format.
Dates and times also are stored as numbers. To see this, enter the date/time “July 15, 2014, 3:00 PM.” Excel stores this internally as the number 41835.625. Although this does not resemble anything you would find on a calendar, it is in fact how Excel stores every date/time, where the portion to the left of the decimal point is the date and the portion to the right is the time. It is only the format applied to the cell that determines how the date/time is displayed. To demonstrate this, apply the General format and the result appears as 41835.625. Apply the Short Date format and you see “7/15/2014.” Regardless of the cell format, the underlying value is still 41835.625.
When you join this with text using the “&” operator, as in the example above, Excel uses the value and ignores the cell format. The same thing happens if you use the CONCATENATE() function.
In the next section, you’ll learn how to include customer-friendly numbers in text.
Using the TEXT() Function
Use TEXT() instead of CONCATENATE() when you need to use a specified format when converting a number to text. The function takes two parameters:
- Value: the number to convert to text
- Format_text: the format to apply to the value
In the example above, try entering in cell A3:
“=the first due at ”&TEXT(D7,“h:mm AM/PM”)
The result is a nicely formatted time:
Below are some of the most common formatting codes available for the TEXT() function.
To format a number, use the following characters in the format_text parameter:
To format a date/time, use the following characters:
To format currency, precede the numeric format with a dollar sign ($):
All other rules for formatting numbers apply to currency.
For other currencies, replace the dollar sign ($) with the appropriate currency sign (£, ¥, or €, for example).
To display a percentage, include a percent sign (%):
All other rules for formatting numbers apply to percentages.
If any of the following characters is included in the format_text argument, the character will appear in the output: $, +, -, /, =, :, ^, ‘,(, ), {, }, <, >, /, !, &, ~, or a space character.
Next Steps
This article covers the basics of using the TEXT() function. Excel offers additional options (such as formatting for the number of hours elapsed) and additional formats not covered here (such as scientific notation). With so many options available, you’re sure to find exactly what you need for any task.
So go ahead and experiment. Design your own formats. Never again will a few extra decimal places stand between you and a well-designed, customer-friendly form.
