Excel Built-in Number Formats
General
Range("A1").NumberFormat = "General"Number
Range("A1").NumberFormat = "0.00"Currency
Range("A1").NumberFormat = "$#,##0.00"Accounting
Range("A1").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"Short Date
Range("A1").NumberFormat = "m/d/yyyy"Long Date
Range("A1").NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy"Time
Range("A1").NumberFormat = "[$-x-systime]h:mm:ss AM/PM"Percentage
Range("A1").NumberFormat = "0.00%"Fraction
Range("A1").NumberFormat = "# ?/?"Scientific
Range("A1").NumberFormat = "0.00E+00"Text
Range("A1").NumberFormat = "@"Special
Zip Code (English US)
Range("A1").NumberFormat = "00000"Zip Code + 4 (English US)
Range("A1").NumberFormat = "00000-0000"Phone Number (English US)
Range("A1").NumberFormat = "[<=9999999]###-####;(###) ###-####"Social Security Number (English US)
Range("A1").NumberFormat = "000-00-0000"Custom Number Formats
Hide zero values
Range("A1").NumberFormat = "0;-0;;@"Hide all values
Range("A1").NumberFormat = ";;;"Symbols for Custom Number Formats
The following table outlines the different symbols available for use in custom number formats.
| Symbol | Description/result |
|---|---|
| 0 | Digit placeholder. For example, if you type 8.9 and you want it to display as 8.90, then use the format #.00 |
| # | Digit placeholder. Follows the same rules as the 0 symbol except Excel does not display extra zeros when the number you type has fewer digits on either side of the decimal than there are # symbols in the format. For example, if the custom format is #.## and you type 8.9 in the cell, the number 8.9 is displayed. |
| ? | Digit placeholder. Follows the same rules as the 0 symbol except Excel places a space for insignificant zeros on either side of the decimal point so that decimal points are aligned in the column. For example, the custom format 0.0? aligns the decimal points for the numbers 8.9 and 88.99 in a column. |
| . (period) | Decimal point. |
| % | Percentage. If you enter a number between 0 and 1, and you use the custom format 0%, Excel multiplies the number by 100 and adds the % symbol in the cell. |
| , (comma) | Thousands separator. Excel separates thousands by commas if the format contains a comma surrounded by '#'s or '0's. A comma following a placeholder scales the number by a thousand. For example, if the format is #.0,, and you type 12,200,000 in the cell, the number 12.2 is displayed. |
| E- E+ e- e+ | Scientific format. Excel displays a number to the right of the "E" symbol that corresponds to the number of places the decimal point was moved. For example, if the format is 0.00E+00 and you type 12,200,000 in the cell, the number 1.22E+07 is displayed. If you change the number format to #0.0E+0 the number 12.2E+6 is displayed. |
| $-+/():space | Displays the symbol. If you want to display a character that is different than one of these symbols, precede the character with a backslash () or enclose the character in quotation marks (" "). For example, if the number format is (000) and you type 12 in the cell, the number (012) is displayed. |
| \ | Display the next character in the format. Excel does not display the backslash. For example, if the number format is 0! and you type 3 in the cell, the value 3! is displayed. |
| * | Repeat the next character in the format enough times to fill the column to its current width. You cannot have more than one asterisk in one section of the format. For example, if the number format is 0*x and you type 3 in the cell, the value 3xxxxxx is displayed. Note, the number of "x" characters displayed in the cell vary based on the width of the column. |
| _ (underline) | Skip the width of the next character. This is useful for lining up negative and positive values in different cells of the same column. For example, the number format (0.0);(0.0) align the numbers 2.3 and -4.5 in the column even though the negative number has parentheses around it. |
| "text" | Display whatever text is inside the quotation marks. For example, the format 0.00 "dollars" displays "1.23 dollars" (without quotation marks) when you type 1.23 into the cell. |
| @ | Text placeholder. If there is text typed in the cell, the text from the cell is placed in the format where the @ symbol appears. For example, if the number format is "Bob "@" Smith" (including quotation marks) and you type "John" (without quotation marks) in the cell, the value "Bob John Smith" (without quotation marks) is displayed. |
| m | Display the month as a number without a leading zero. |
| mm | Display the month as a number with a leading zero when appropriate. |
| mmm | Display the month as an abbreviation (Jan-Dec). |
| mmmm | Display the month as a full name (January-December). |
| d | Display the day as a number without a leading zero. |
| dd | Display the day as a number with a leading zero when appropriate. |
| ddd | Display the day as an abbreviation (Sun-Sat). |
| dddd | Display the day as a full name (Sunday-Saturday). |
| yy | Display the year as a two-digit number. |
| yyyy | Display the year as a four-digit number. |
| h | Display the hour as a number without a leading zero. |
| [h] | Elapsed time, in hours. If you are working with a formula that returns a time where the number of hours exceeds 24, use a number format similar to [h]:mm:ss. |
| hh | Display the hour as a number with a leading zero when appropriate. If the format contains AM or PM, then the hour is based on the 12-hour clock. Otherwise, the hour is based on the 24-hour clock. |
| m | Display the minute as a number without a leading zero. |
| [m] | Elapsed time, in minutes. If you are working with a formula that returns a time where the number of minutes exceeds 60, use a number format similar to [mm]:ss. |
| mm | Display the minute as a number with a leading zero when appropriate. The m or mm must appear immediately after the h or hh symbol, or Excel displays the month rather than the minute. |
| s | Display the second as a number without a leading zero. |
| [s] | Elapsed time, in seconds. If you are working with a formula that returns a time where the number of seconds exceeds 60, use a number format similar to [ss]. |
| ss | Display the second as a number with a leading zero when appropriate.Note that if you want to display fractions of a second, use a number format similar to h:mm:ss.00. |
| AM/PM, am/pm, A/P, a/p | Display the hour using a 12-hour clock. Excel am/pm displays AM, am, A, or a for times from midnight A/P until noon, and PM, pm, P, or p for times from noon a/p until midnight. |