VBA Format function

Format Function Description

The VBA Format function formats a string as according to the provided format. The Format function uses the locale as according to your system settings. To format locale specific formulas read on.

VBA Reference – String Functions

Syntax

The syntax for the Format function in VBA is:

Format ( expression, [ format ] )

Parameters

expression
The string you want to format.

format
Optional. The designated format for the expression string. This can be either:

  • Custom format
  • Predefined format – one of the predefined Excel string formats:
    Format Description
    General Number Number without thousand separators.
    Currency Currency with thousand separators and two decimal places
    Fixed Number with at least one integer digit and two decimal places
    Standard Number with thousand separators, at least one integer digit and two decimal places
    Percent Percentage – percent integer with 2 decimal places
    Scientific Number in scientific notation
    Yes/No No if number is 0. Yes otherwise
    True/False False if 0. True otherwise
    On/Off Off if 0. On otherwise
    General Date Date based on locale
    Long Date Date based on system’s long date setting
    Medium Date Date based on system’s medium date setting
    Short Date Date based on system’s short date setting
    Long Time Date based on system’s long time setting
    Medium Time Date based on system’s medium time setting
    Short Time Time based on system’s short time setting

Other Notes

The Format function formats strings according to your current set locale. If you want to format your strings according to a particular language/locale use the WorksheetFunction.Text function instead:

WorksheetFunction.Text(Date,"[$-486]mmm")
'Result: "Jan"

WorksheetFunction.Text(Date,"[$-040C]mmm")
'Result: "janv."

Notice that you need to precede the format parameter with the following string:

[$-LOCALE] 'Local = Hex LCID Dec

You can find the full list of language/locale codes on MSDN here.

Example usage

The Format function can only be used in VBA code. Let’s look at some VBA Format function examples:

Format("20.11", "#,#0.0")
'Result: "20.1"

Format(Date, "yyyy-mm"))
'Result: "2016-01"