Formatting cells to display data properly is an essential skill that with practice becomes natural and sure. But even an experienced user like me who becomes careless or rushed can produce clumsy and confusing results. There's no built-in governance to ensure that format matches data, so you can have a column of a consistent data type but inconsistent format. Cut and paste operations further risk format confusion, since format is included by default. Even the best ways to mitigate the mess, such as table structures and paste values, still rely on discipline and inspection.
As we think about writing functions to populate the sheet with data, why not use them to format it at the same time? If the output is a column of dates, they can just as easily be formatted to look like dates. Then there's no need to change the cell number format. As you assemble and format the function output array, each column is inherently consistent. By outputting both value and format we can liberate the sheet from its inherent liability, while saving the time and effort to format and govern cells.
Now wait a minute, you're thinking, I know I can format data with the TEXT function, but then the output is text and can't be further manipulated. True, but your custom function has already done the necessary manipulation and can be modified to do more if needed. You are consolidating the processing and formatting of many cells into one name in one cell. If you still need numeric values, for example, for conditional formatting, the condition can use VALUE to convert the text back to number for evaluation.
Change Number Format
Of all the cell formatting options, the one you can set by function is the Number format. This is done by converting numeric values to text representations in the desired format, with TEXT(value, format_text). The format_text argument uses a lexicon of symbols that must be learned. They are mostly intuitive and can be learned by example in the Custom format category and learned in full in Microsoft help.
Test: a General-format cell was filled with the current date by Ctrl+; and was automatically formatted as a date cell. I see the cell format when I open the Format Cells dialog (Ctrl+1), Number tab, which highlights the Category: Date, and the Type: *3/14/2012 (short date format in system settings). Here as in all eleven pre-defined format categories, a fixed set of options are given as example outputs to describe the format; they do not reveal the code required for the TEXT function.
The Custom category is different, allowing you to type in any format code that follows the defined syntax, and even suggesting that you learn by doing, "using one of the existing
codes as a starting point." As seen here, the same cell as above is now shown with the format code, Type: m/d/yyyy. You see that the letters and syntax are intuitive, but you see other types with various symbols that are not intuitive: 0, #, _, ?, etc. These characters and their usage in the format syntax, the rules of the four sections + ; - ; 0 ; text, conditions, and font color, all can be learned from the help link given above, and you will soon be quite fluent in format codes.
By setting cell format using any pre-defined format (first eleven categories), you can then switch to the Custom category and copy the code from the Type: input box to the clipboard, to be pasted into the TEXT function. If you need further customization, or if you're comfortable typing your own code, you can then edit the code directly.
If you've defined enough custom format codes you might like to create a "cheat array", as described in my recent post, as a memory aid. Here's one for example, with sample formats in each category, sample values and TEXT outputs, hotkeys, CELL("format") output, and custom function f.cell() output.
As shown below, the sample values in column 4 (value, line 594), or [input] if provided, are converted to text in column 5 (line 606) using the format code in column 3 (line 600); [input] can be a single value (596), a min/max range or array (597), a full array (598), or blank (599). The table is reassembled column by column (603-607), and header added (601-602).
Some cell formatting features cannot be replicated via TEXT:
TEXT cannot align data in a column such as by decimal point, currency symbol, or fraction symbol.
TEXT cannot change font color.
If quotation marks exist in the format string, they must be repeated within TEXT to interpret them as quotes within the overall quoted format string.
Usage
The most obvious case for format by function is dates, because the actual date values are meaningless in themselves. I have used TEXT to output legible dates in many functions. The watchout is when sharing functions across regions having different date format conventions, to allow users to easily adopt the function with their own format. An easy solution is an optional format argument, and/or instructions to edit the default.
Other examples are currency and fixed decimals. Since TEXT output is text, it aligns left by default. If helpful, use cell format to align right, then fixed decimals aligns the decimal point.
Example: fractions
Here's a cheat array, a list of fractional inches with decimal and millimeter equivalents. The function allows input of the power of 2, from 0 to 7, and splits one inch to that many parts (default 4 = 16ths). It has three output options and allows setting mm rounding digits.
In the code below, the fraction decimals are calculated in line 353, converted to mm in line 355, and formatted as fractions in line 356 with code "???/???", allowing up to power 7 = 128ths.
Write Sentences
Since TEXT output is text, it can be concatenated with other strings to write sentences. A simple example:
Another example is unit conversion. The native CONVERT function does the math but does not include the units. This custom function outputs a sentence with rounded value by default, or a number with optional argument.
Comments