The DOLLAR function will convert any number into text while applying the standard dollar currency format.
Contents:
Example 1 - Converting a Number to Text Formatted as Dollars
Example 2 - Display Dollars Correctly in Concatenated Text Formula
Syntax
= DOLLAR(number, [decimals])number = the number to be converted into text
[decimals] = optional argument, a value specifying the precision of rounding. The default value is 2 decimal places.
Explanation
The DOLLAR function is part of the "Text" group of functions within Excel.
This function will take any number and convert that number into text while applying the local currency formatting.
The optional decimals argument can be used to round
A positive value will change the rounding after the decimal, 1 being tenths place, 2 being hundredths place, and so on.
Using negative numbers will round upwards before the decimal. A -1 will round to the nearest tens place, -2 the nearest hundred, -3 the nearest thousand, and so on, all within the function.
Notes:
- If the input is non-numerical, a #VALUE! error will be returned
- A blank cell will be returned as a 0
Examples
1. How to Convert a Number to Text and Format as a Currency Using a Formula
Converting numbers into text formatted as a currency is the primary use of the DOLLAR function. Every cell in the "Converted" column will be formatted as text, and not a number.
In the below example, we have a table of monthly sales. This formula takes any number as an input, then formats that number and rounds to the specified number of places.
The default rounding behavior, as seen in the first row, will round to 2 decimal places. Changing this argument will move the rounding up or down and can even be used to round upwards to the nearest hundred, thousand, and upwards.
= DOLLAR(amount, rounding)
2. How to Display Dollars Correctly in a Concatenated Text Formula
The DOLLAR function can also be useful when trying to format concatenated formulas.
The standard behavior when using the "&" symbol to join two separate formula pieces together, is to wipe the formatting. This can be a struggle when combining numbers with text.
= B25 & " " & C25 & " were sold at " & DOLLAR(D25)
This isn't desirable as if we didn't include the DOLLAR function in this example, we would have been left with an unformatted "2":
By adding the DOLLAR function, the formula now displays the sale price the correct way.