VBA Format Function in Excel: 8 Uses with Methods

Method 1 – VBA to Format Numbers with Predefined Formats

Below is the VBA code which covers all the predefined formats to Format numbers in Excel. The predefined formats are Currency, Fixed, Standard, Percent, Scientific, Yes/No, True/False, and On/Off.

Sub PreDefinedNumFormat()
MsgBox Format(12345678.9)
'Result: 12345678.9
'Format: General
'Just leaves the number as it is
MsgBox Format(12345678.9, "Currency")
'Result: $12,346,678.90
'Format: Currency
'Uses the systems currency settings
MsgBox Format(12345678.9, "Fixed")
'Result: 12345678.90
'Format: Fixed
'At least one digit before the decimal point and
'uses system settings for the decimal place after
MsgBox Format(12345678.9, "Standard")
'Result is: 12,345,678.90
'Format: Standard
'Thousand separators, decimal values and standard system settings
MsgBox Format(12345678.9, "Percent")
'Result: 1234567890.00%
'Format: Percent
'Multiplies by 100 with % symbol and standard system settings
MsgBox Format(12345678.9, "Scientific")
'Result: 1.23E+07
'Format: Scientific
'Displays the result with scientific notation
MsgBox Format(12345678.9, "Yes/No")
'Result: Yes
'Format: Yes/No
'No, if the number is zero; Yes otherwise
MsgBox Format(12345678.9, "True/False")
'Result: True
'Format: True/False
'False if the number is zero; True otherwise
MsgBox Format(12345678.9, "On/Off")
'Result: On
'Format: On/Off
'Off if the number is zero; On otherwise
End Sub

Run this code and get the result of every predefined number format in the Excel message box.

Method 2 – Macro to Format User-Defined Numbers

Excel’s Format function can convert user-defined numbers to strings.

Where,

0 displays a digit or zero
# displays a digit or nothing
A dot (.) works as the decimal placeholder
% is the percentage placeholder
The comma (,) is the thousands separator
Double quotes (“”) are used to add texts
The backslash (\) is used after a single character is added.

The VBA code with the implementation of these is shown below.

Sub UserDefinedNumFormat()
MsgBox Format(1.2, "000.00")
'Result: 001.20
MsgBox Format(12345.6789, "000.00")
'Result: 12345.68
MsgBox Format(1.2, "###.##")
'Result: 1.2
MsgBox Format(12345.6789, "###.##")
'Result: 12345.68
MsgBox Format(1.2, "\$.00")
'Result: $1.20
MsgBox Format(1234.567, "AABB0.00")
'Result: AABB1234.57
MsgBox Format(12345.6789, "000.00%")
'Result: 1234567.89%
MsgBox Format(12345.6789, "%000.00")
'Result: %12345.68
End Sub

Run this code and get the result of all the user-defined number formats in the Excel message box that you provided in the code.

Method 3 – Embed VBA to Format Numbers Based on Values

Format function has different rules to work with positive numbers, negative numbers, zero and Null values. These values are separated by a semicolon.

Sub FormatNumBasedOnValue()
MsgBox Format(1.2, "000.00;(000.00);\z\e\r\o;nothing")
'Result: 001.20
MsgBox Format(-1.2, "000.00;(000.00);\z\e\r\o;nothing")
'Result: (001.20)
MsgBox Format(0, "000.00;(000.00);\z\e\r\o;nothing")
'Result: zero
MsgBox Format(Null, "000.00;(000.00);\z\e\r\o;nothing")
'Result: nothing
End Sub

Run this code and get the result for each format.

Method 4 – VBA to Format Dates with Predefined Formats in Excel

In Excel, dates have different built-in formats; the Format function can format those dates too. These formats are Long date, Medium date, Short date, Long time, Medium time and Short time.

Sub PredefinedDateFormat()
Dim iDate As Date
iDate = #1/31/2022 3:31:56 PM#
MsgBox Format(iDate, "General Date")
'Result: 1/31/2022 3:31:56 PM
MsgBox Format(iDate, "Long Date")
'Result: Monday, January 31, 2022
MsgBox Format(iDate, "Medium Date")
'Result: 31-Jan-22
MsgBox Format(iDate, "Short Date")
'Result: 1/31/2022
MsgBox Format(iDate, "Long Time")
'Result: 3:31:56 PM
MsgBox Format(iDate, "Medium Time")
'Result: 03:31 PM
MsgBox Format(iDate, "Short Time")
'Result: 15:31
End Sub

Run this code get the result of every predefined date format in Excel message-box.

Method 5 – Macro to Format User-Defined Dates in Excel

Excel’s Format function can convert dates with user-defined formats. Characters such as d, m, y, w, q are usually used to create custom date formats.

Sub UserDefinedDateFormat()
Dim iDate As Date
iDate = #1/31/2022 3:31:56 PM#
MsgBox Format(iDate, "m/d/yy")
'Result: 1/31/2022
MsgBox Format(iDate, "mm-dd-yy")
'Result: 01-31-22
MsgBox Format(iDate, "mmm-dd-yy")
'Result: Jan-31-22
MsgBox Format(iDate, "mmmm-dd-yyyy")
'Result: January-31-2022
MsgBox Format(iDate, "mm-ddd-yy")
'Result: 01-Mon-22
MsgBox Format(iDate, "mm-dddd-yyyy")
'Result: 01-Monday-2022
MsgBox Format(iDate, "y")
'Result: 31
'Number of day in year 1-366
MsgBox Format(iDate, "ww")
'Result: 6
'Number of weeks in year 1-52
MsgBox Format(iDate, "q")
'Result: 1
'Quarter in year 1-4
End Sub

Run this code and get the result of all the user-defined date formats in the Excel message box that you provided here.

Method 6 – VBA Macro to Customize Time Format in Excel

Format function can convert time with user-defined formats. Characters such as h, n, s, am and pm are usually used to create custom time formats.

Sub CustomTimeFormat()
Dim iDate As Date
iDate = #1/31/2022 3:01:06 PM#
MsgBox Format(iDate, "h:n:s")
'Result: 15:1:6
MsgBox Format(iDate, "hh:nn:ss")
'Result: 15:01:06
MsgBox Format(iDate, "hh:nn:ss am/pm")
'Result: 03:01:06 pm
MsgBox Format(iDate, "hh:nn:ss AM/PM")
'Result: 03:01:06 PM
MsgBox Format(iDate, "hh:nn:ss a/p")
'Result: 03:01:06 p
MsgBox Format(iDate, "hh:nn:ss A/P")
'Result: 03:01:06 P
End Sub

Run this code get the result of all the user-defined time formats in the Excel message-box that you provided in the code.

Method 7 – Embed VBA to Format Text in Excel

The Format function can also be used to change the format of a text value inside the VBA code. To get the same result, we have to use WorksheetFunction.Text.

Sub FormatText()
MsgBox Format(1.2, "000.00")
'Result: 001.20
MsgBox WorksheetFunction.Text(1.2, "000.00")
'Result: 001.20
MsgBox Format(1.2, "###.##")
'Result: 1.2
MsgBox WorksheetFunction.Text(1.2, "###.##")
'Result: 1.2
End Sub

Notice the similarities between both of the functions in the picture shown below.

Method 8 – VBA to Format String or Characters in Excel

The format function can format any string or character to make the data more user-friendly without changing the original value.

@ displays a character or space
& displays a character or nothing
! displays results from left to right
< or > is used to enforce lower or upper-case

This process helps update telephone numbers or other large numbers for better readability.

Sub FormatString()
Dim iStr As String
iStr = "XYZabc"
MsgBox Format(iStr, "-@@@-@@-@@")
'Result: - XY-Za-bc
MsgBox Format(iStr, "-&&&-&&-&&")
'Result: -XY-Za-bc
MsgBox Format(iStr, "-@@@-@@-@@-@@")
'Result: -  -XY-Za-bc
'When out of characters @ adds spaces and & adds nothing
MsgBox Format(iStr, "-&&&-&&-&&-&&")
'Result: --XY-Za-bc
'When out of characters @ adds spaces and & adds nothing
MsgBox Format(iStr, "!-@@@-@@-@@-@@")
'Result: -XYZ-ab-c -
MsgBox Format(iStr, "!-&&&-&&-&&-&&")
'Result: -XYZ-ab-c
MsgBox Format(iStr, ">")
'Result: XYZABC
MsgBox Format(iStr, "<")
'Result: xyzabc
MsgBox Format(1234567890, "@@@-@@@-@@@@")
'Result: 123-456-7890
MsgBox Format(1234567890, "@@@@-@@@-@@@")
'Result: 1234-567-890
End Sub