MS-Excel / Functions and Formula

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.
Constants that specify how a year starts 
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:

Format("12345", "Currency")

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:

Format("123456", "$00.0000")

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.

Characters for creating your own date and time formats 
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:

Format(#4/1/2010#, "dddddd")

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:"), "@@@@")
[Previous] [Contents] [Next]

In this tutorial:

  1. Using Built-in Functions
  2. What Is a Function?
  3. Passing Arguments to a Function
  4. Using Functions to Convert Data from One Type to Another
  5. Using the Asc Function to Return a Character Code
  6. Using the Str Function to Convert a Value to a String
  7. Using the Format Function to Format an Expression
  8. Using the Chr Function and Constants to Enter Special Characters in a String
  9. Using Functions to Manipulate Strings
  10. Using InStr and InStrRev to Find a String within Another String
  11. Using LTrim, RTrim, and Trim to Trim Spaces from a String
  12. Using Len to Check the Length of a String
  13. Using the StrComp Function to Compare Apples to Apples
  14. Using VBA's Mathematical Functions
  15. Excel VBA's Date and Time Functions
  16. Using the DateDiff Function to Return an Interval
  17. Using File-Management Functions