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!
1 comment:
Bob, you should add a search this blog gizmo in this blog. It is part of blogger's fields you can add.
Unrelated to this post, here is a trick I like: say you want to have a ROW of formulas that refer to cells of values in a COLUMN so that A1=D4, B1=D5,C1=D6, etc. In Column E, make E4=$D4, E5=$D5, E6=$D6, etc (notice the $).
Copy the range of cells with these formulas in column E.
Go to Cell A1. Paste Special. Select Transpose. Select Formulas. Hit enter. BAM! Now, from A1 to B1, etc, you have the formulas referring to the COLUMN of data that begins in D4!
Post a Comment