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.
The syntax for the Format function in VBA is:
Format ( expression, [ format ] )
The string you want to 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
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.
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"