Excel-VBA : Math Functions – FORMAT()

Description: The FORMAT() function in MS excel takes a number and returns it as formatted string based on specified parameter.

Format:

VBA Function : FORMAT (number,[format])

Arguments:

  • Number
      • Mandatory
      • Type: number
      • number for which the formatted string will be returned

  • Format
    • Optional
    • Type: string expression
    • format in which the number will be returned.

 

Formats

FormatDetails
General NumberThis format displays a number without thousand separators.
CurrencyThis format displays a number with thousand separators, along with two decimal places.
FixedThis format displays at least one digit to the left of the decimal place and two digits to the right of the decimal place.
StandardThis format displays the thousand separators, at least one digit to the left of the decimal place, and two digits to the right of the decimal place.
PercentThis format displays a percent value – with 2 decimal places
ScientificThis format uses Scientific notation.
Yes/NoThis format displays No if the number is 0 and displays Yes if the number is not 0.
True/FalseThis format displays True if the number is 0 and displays False if the number is not 0.
On/OffThis format displays Off if the number is 0 and displays On is the number is not 0.

 Examples:

NumberFormatOutput
110.2Standard110.2
120.5Currency$120.5
0.44.2Precent44.20%
78Yes/NoYes
121212True/FalseTRUE
0On/OffOff

Example:

Function getFormat()
    val1 = 110.2
    val2 = 133.9
    val3 = 0.882
    val4 = 12345
    val5 = 12
    val6 = 0
    strResult = "The FORMAT of " & val1 & " in Standrad is : " & Format(val1, "Standard") & vbCrLf
    strResult = strResult & "The FORMAT of " & val2 & " in Currency is : " & Format(val2, "Currency") & vbCrLf
    strResult = strResult & "The FORMAT of " & val3 & " in Percent is : " & Format(val3, "Percent") & vbCrLf
    strResult = strResult & "The FORMAT of " & val4 & " in Yes/No is : " & Format(val4, "Yes/No") & vbCrLf
    strResult = strResult & "The FORMAT of " & val5 & " in True/False is : " & Format(val5, "true/False") & vbCrLf
    strResult = strResult & "The FORMAT of " & val6 & " in On/Off is : " & Format(val6, "On/Off") & vbCrLf
    MsgBox strResult
End Function
FORMAT-VBA
FORMAT-VBA