Using the Format Function to Format an Expression
The Format function is a powerful tool for changing numbers, dates and times, and strings into the format that you want.
The syntax for the Format function is as follows:
Format(expression[, format[, firstdayofweek[, firstweekofyear]]])
These are the components of the syntax:
- expression is any valid expression.
- format is an optional argument specifying a named format expression or a user-defined format expression. More on this in a moment.
- firstdayofweek is an optional constant specifying the day that starts the week (for date information): The default setting is vbSunday (1), but you can also set vbMonday (2), vbTuesday (3), vbWednesday (4), vbThursday (5), vbFriday (6), vbSaturday (7), or vbUseSystem (0; uses the system setting).
- firstweekofyear is an optional constant specifying the week considered first in the year (again, for date information), as shown in Table below.
Constant Value Year Starts with Week vbUseSystem 0 Use the system setting. vbFirstJan1 1 The week in which January 1 falls (the default setting). vbFirstFourDays 2 The first week with a minimum of four days in the year. vbFirstFullWeek 3 The first full week (seven days) of the year.
You can define your own formats for the Format function as described in the following sections if none of the predefined numeric formats (described next) suits your needs.USING PREDEFINED NUMERIC FORMATS
Table below lists the predefined numeric formats that you can use with the Format function.Predefined numeric formats
Format Name Explanation Example General Number The number is displayed with no 124589 housand separator. Currency The number is displayed with two $1,234.56 decimal places, a thousand separator, and the currency symbol appropriate to the system locale. Fixed The number is displayed with two 5.00 decimal places and at least one integer place. Standard The number is displayed with two 1,225.00 decimal places, at least one integer place, and a thousand separator (when needed). Percent The number is displayed multiplied by 78.00% 100, with two decimal places and with a percent sign. Scientific The number is displayed in scientific 5.00E+00 notation. Yes/No A non-zero number is displayed as Yes; Yes a zero number is displayed as No. True/False A nonzero number is displayed as False True; a zero number is displayed as False. On/Off A nonzero number is displayed as On; Off a zero number is displayed as Off.
For example, the following statement returns $123.45:
CREATING A NUMERIC FORMAT
If none of the predefined numeric formats suits your needs, you can create your own numeric formats by using your choice of a combination of the characters listed in Table below.Characters for creating your own number formats
Character Explanation [None] Displays the number without any formatting. (You won't usually want to use this option.) 0 Placeholder for a digit. If there's no digit, VBA displays a zero. If the number has fewer digits than you use zeroes, VBA displays leading or trailing zeroes as appropriate. # Placeholder for a digit. If there's no digit, VBA displays nothing. . Placeholder for a decimal. Indicates where the decimal separator should fall. The decimal separator varies by locale (for example, a decimal point in the United States, a comma in Germany). % Placeholder for a percent character. VBA inserts the percent character and multiplies the expression by 100. , Thousand separator (depending on locale, a comma or a period). : Time separator (typically a colon, but again this depends on the locale). / Date separator. (Again, what you'll see depends on the locale.) E- E+ e- e+ Scientific format: E- or e- places a minus sign next to negative exponents. E+ or e+ places a minus sign next to negative exponents and places a plus sign next to positive exponents. - + $ ( ) Displays a literal character. \[character] Displays the literal character. "[string]" Displays the literal character. Use Chr(34) (the character code for double quotation marks) to provide the double quotation marks.
For example, the following statement returns a currency formatted with four decimal places:
CREATING A DATE OR TIME FORMAT
Similarly, you can create your own date and time formats by mixing and matching the characters listed in Table below.
Character Explanation : Time separator (typically a colon, but this depends on the locale). / Date separator (also locale dependent). C Displays the date (if there is a date or an integer value) in the system's short date format and the time (if there is a date or a fractional value) in the system's default time format. D Displays the date (1 to 31) without a leading zero for single-digit numbers. Dd Displays the date with a leading zero for single-digit numbers (01 to 31). Ddd Displays the day as a three-letter abbreviation (Sun, Mon, Tue, Wed, Thu, Fri, Sat) with no period. Dddd Displays the full name of the day. Ddddd Displays the complete date (day, month, and year) in the system's short date format. Dddddd Displays the complete date (day, month, and year) in the system's long date format. aaaa Displays the full, localized name of the day. w Displays an integer from 1 (Sunday) to 7 (Monday) containing the day of the week. ww Displays an integer from 1 to 54 giving the number of the week in the year. The number of weeks is 54 rather than 52 because most years start and end with partial weeks rather than having 52 start-to-finish weeks. m Displays an integer from 1 to 12 giving the number of the month without a leading zero on single-digit months. When used after h, returns minutes instead of months. mm Displays a number from 01 to 12 giving the two-digit number of the month. When used after h, returns minutes instead of months. mmm Displays the month as a three-letter abbreviation (except for May) without a period. mmmm Displays the full name of the month. oooo Displays the full localized name of the month. q Displays a number from 1 to 4 giving the quarter of the year. y Displays an integer from 1 to 366 giving the day of the year. yy Displays a number from 00 to 99 giving the two-digit year. yyyy Displays a number from 0100 to 9999 giving the four-digit year. h Displays a number from 0 to 23 giving the hour. Hh Displays a number from 00 to 23 giving the two-digit hour. N Displays a number from 0 to 60 giving the minute. Nn Displays a number from 00 to 60 giving the two-digit minute. S Displays a number from 0 to 60 giving the second. Ss Displays a number from 00 to 60 giving the two-digit second. ttttt Displays the full time (hour, minute, and second) in the system's default time format. AM/PM Uses the 12-hour clock and displays AM or PM as appropriate. am/pm Uses the 12-hour clock and displays am or pm as appropriate. A/P Uses the 12-hour clock and displays A or P as appropriate. a/p Uses the 12-hour clock and displays a or p as appropriate. AMPM Uses the 12-hour clock and displays the AM or PM string literal defined for the system.
For example, the following statement returns Saturday, April 01, 2010:
CREATING A STRING FORMAT The Format function also lets you create custom string formats using the options shown in Table below.Characters for creating your own string formats
Character Explanation @ Placeholder for a character. Displays a character if there is one and a space if there is none. & Placeholder for a character. Displays a character if there is one and nothing if there is none. < Displays the string in lowercase. > Displays the string in uppercase. ! Causes VBA to fill placeholders from left to right instead of from right to left (the default direction).
For example, the following statement assigns to strUser a string consisting of four spaces if there is no input in the input box:
strUser = Format(InputBox("Enter your name:"), "@@@@")
In this tutorial:
- Using Built-in Functions
- What Is a Function?
- Passing Arguments to a Function
- Using Functions to Convert Data from One Type to Another
- Using the Asc Function to Return a Character Code
- Using the Str Function to Convert a Value to a String
- Using the Format Function to Format an Expression
- Using the Chr Function and Constants to Enter Special Characters in a String
- Using Functions to Manipulate Strings
- Using InStr and InStrRev to Find a String within Another String
- Using LTrim, RTrim, and Trim to Trim Spaces from a String
- Using Len to Check the Length of a String
- Using the StrComp Function to Compare Apples to Apples
- Using VBA's Mathematical Functions
- Excel VBA's Date and Time Functions
- Using the DateDiff Function to Return an Interval
- Using File-Management Functions