The FORMAT function is often used in various programming and database environments (like SQL Server, DAX in Power BI, Excel, etc.) to format numeric values according to specific patterns. Here's how to use it effectively in different contexts:
SQL Server
In SQL Server, the FORMAT function can be used to format numeric values as follows:
SELECT FORMAT(1234567.89, 'N', 'en-US') AS FormattedNumber;
Parameters:
- The first parameter is the numeric value you want to format.
- The second parameter is a format string, where:
- 'N' stands for number format, which includes comma as the thousands separator and two decimal places.
- The third parameter is optional and specifies the culture/locale to use, such as 'en-US' for US English.
Common Format Strings:
- Currency: 'C' – formats as currency (e.g., $1,234.56)
- Percentage: 'P' – formats as a percentage (e.g., 123,456.79%)
- Scientific: 'E' – formats in scientific notation (e.g., 1.23E+03)
DAX (Power BI, Excel)
In DAX, you don't have a direct FORMAT function for numeric values like SQL. Instead, the FORMAT function is used like this:
FormattedNumber = FORMAT(1234567.89, "N0")
Parameters:
- The first parameter is the numeric value.
- The second parameter is the format string:
- "N0" means format as a number with no decimal places.
- You can specify other strings similar to Excel formats, such as "C" (currency), "P" (percentage).
Excel
In Excel, you would typically use the TEXT function rather than FORMAT, but the usage is similar:
=TEXT(1234567.89, "0,0.00")
Parameters:
- The first argument is the value to format.
- The second argument is the format code:
- "0,0.00" indicates that the number should be formatted with comma as the thousands separator and two decimal places.
Summary
The use of the FORMAT function varies depending on the context, but the core idea remains similar: specify the numeric value and the desired format string. Always check the documentation for the specific environment you're using to understand the formatting options available, as they may differ slightly in syntax and capabilities.