# 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
```