Wednesday, February 16, 2011
Using the TEXT Function
Although Excel is all about working with numbers, there are times when you want to be able to manipulate your data into specially formatted Text. The solution is the TEXT function! By using special format strings, TEXT allows you to specify how you display the numerical value in text with formatting of your choice.
The TEXT syntax is: TEXT(value, format_string)
For example, the format_string $#,##0 for the second part of the TEXT function provides a monetary formatting with a dollar sign and comma that makes for immediate comprehension.
TEXT can be used with Concatenation to return user-friendly (especially if the user is not particularly spreadsheet savvy) reports. For example, in the illustration below, the following formula was created in the merged cells of A6 and B6:
="The Net Profit is"&TEXT(B4," $#,##0")
The outcome is a cleanly formatted statement as to the resulting profit (something even your boss can understand…). Happy Excelling All!