Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

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

Format Details
General Number This format displays a number without thousand separators.
Currency This format displays a number with thousand separators, along with two decimal places.
Fixed This format displays at least one digit to the left of the decimal place and two digits to the right of the decimal place.
Standard This 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.
Percent This format displays a percent value – with 2 decimal places
Scientific This format uses Scientific notation.
Yes/No This format displays No if the number is 0 and displays Yes if the number is not 0.
True/False This format displays True if the number is 0 and displays False if the number is not 0.
On/Off This format displays Off if the number is 0 and displays On is the number is not 0.

 Examples:

Number Format Output
110.2 Standard 110.2
120.5 Currency $120.5
0.44.2 Precent 44.20%
78 Yes/No Yes
121212 True/False TRUE
0 On/Off Off

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

 

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: