Use formatting symbols in conjunction with the TEXT() function to format text strings. The following table lists the format symbols that can be used in a custom format string.
| Symbol | Description |
|---|---|
| General | Displays the number in General format. |
| 0 |
Digit placeholder. If the number contains fewer digits than
the format contains placeholders, the number is padded with 0's. If there are more digits to the right of the decimal than there are placeholders, the decimal portion is rounded to the number of places specified by the placeholders. If there are more digits to the left of the decimal than there are placeholders, the extra digits are retained. |
| # | Digit placeholder. This placeholder functions the same as the 0 placeholder except the number is not padded with 0's if the number contains fewer digits than the format contains placeholders. |
| ? | Digit placeholder. This placeholder functions the same as the 0 placeholder except that spaces are used to pad the digits. |
| . (period) |
Decimal point. Determines how many digits (0's or #'s) are
displayed on either side of the decimal point. If the format contains only #'s left of the decimal point, numbers less than 1 begin with a decimal point. If the format contains 0's left of the decimal point, numbers less than 1 begin with a 0 left of the decimal point. |
| , (comma) |
Thousands separator. If the format contains commas separated
by #'s or 0's, the number is displayed with commas separating thousands.
A comma following a placeholder scales the number by a thousand. For example, the format 0, scales the number by 1000 (e.g., 10,000 would be displayed as 10). |
| $ — + / ( ) : space | Displays that character. To display a character other than those listed, precede the character with a back slash (\). You can also use the slash (/) for fraction formats. |
| \ | Displays the next character. The backslash is not displayed. |
| m | Month number. Displays the month as digits without leading zeros (e.g., 1-12). Can also represent minutes when used with h or hh formats. |
| mm | Month number. Displays the month as digits with leading zeros (e.g., 01-12). Can also represent minutes when used with the h or hh formats. |
| mmm | Month abbreviation. Displays the month as an abbreviation (e.g., Jan-Dec). |
| mmmm | Month name. Displays the month as a full name (e.g., January-December). |
| d | Day number. Displays the day as digits with no leading zero (e.g., 1-2). |
| dd | Day number. Displays the day as digits with leading zeros (e.g., 01-02). |
| ddd | Day abbreviation. Displays the day as an abbreviation (e.g., Sun-Sat). |
| dddd | Day name. Displays the day as a full name (e.g., Sunday-Saturday). |
| yy | Year number. Displays the year as a two-digit number (e.g., 00-99). |
| yyyy | Year number. Displays the year as a four-digit number (e.g., 1900-9999). |
| h | Hour number. Displays the hour as a number without leading zeros (e.g., 1-23). If the format contains one of the AM or PM formats, the hour is based on a 12-hour clock. Otherwise, it is based on a 24-hour clock. |
| hh |
Hour number. Displays the hour as a number with leading
zeros (e.g., 01-23). If the format contains one of the AM or PM formats, the hour is based on a 12-hour clock. Otherwise, it is based on a 24-hour clock. |
| m | Minute number. Displays the minute as a number without leading zeros (e.g., 0-59). The m format must appear immediately after the h or hh symbol. Otherwise, it is interpreted as a month number. |
| mm | Minute number. Displays the minute as a number with leading
zeros (e.g., 00-59). The mm format must appear immediately after the h or hh symbol. Otherwise, it is interpreted as a month number. |
| s | Second number. Displays the second as a number without leading zeros (e.g., 0-59). |
| ss | Second number. Displays the second as a number with leading zeros (e.g., 00-59). |
| AM/PM am/pm A/P a/p | 12-hour time. Displays time using a 12-hour clock. Displays AM, am, A, or a for times between midnight and noon; displays PM, pm, P, or p for times from noon until midnight. |