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()

Descrip­tion: The FORMAT() func­tion in MS excel takes a num­ber and returns it as for­mat­ted string based on spec­i­fied parameter.

For­mat:

VBA Func­tion : FORMAT (number,[format])

Argu­ments:

  • Num­ber
      • Manda­tory
      • Type: num­ber
      • num­ber for which the for­mat­ted string will be returned

  • For­mat
    • Optional
    • Type: string expression
    • for­mat in which the num­ber will be returned.

 

For­mats

For­mat Details
Gen­eral Number This for­mat dis­plays a num­ber with­out thou­sand separators.
Cur­rency This for­mat dis­plays a num­ber with thou­sand sep­a­ra­tors, along with two dec­i­mal places.
Fixed This for­mat dis­plays at least one digit to the left of the dec­i­mal place and two dig­its to the right of the dec­i­mal place.
Stan­dard This for­mat dis­plays the thou­sand sep­a­ra­tors, at least one digit to the left of the dec­i­mal place, and two dig­its to the right of the dec­i­mal place.
Per­cent This for­mat dis­plays a per­cent value — with 2 dec­i­mal places
Sci­en­tific This for­mat uses Sci­en­tific notation.
Yes/No This for­mat dis­plays No if the num­ber is 0 and dis­plays Yes if the num­ber is not 0.
True/False This for­mat dis­plays True if the num­ber is 0 and dis­plays False if the num­ber is not 0.
On/Off This for­mat dis­plays Off if the num­ber is 0 and dis­plays On is the num­ber is not 0.

 Examples:

Num­ber For­mat Out­put
110.2 Stan­dard 110.2
120.5 Cur­rency $120.5
0.44.2 Pre­cent 44.20%
78 Yes/No Yes
121212 True/False TRUE
0 On/Off Off

Exam­ple:

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: