I have had quite a few use cases where I needed to use a formula to calculate an amount or amounts then join that amount into a text string, but when you do that, you lose your separators. If you want dollars, you also end up with no cents or single digit cents which can look a little wonky.
The below formulas can reference a cell containing a number and output that number as a string with "$" and "," in place. You can either drop this into its own cell and reference it in your string, or you can include the formula in the formula outputting the string.
Positive numbers only with "$" and two digit cents:
"$12,345.90"
="$" + RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(RIGHT("000000000000000" + INT(Amount@row), 15), 3, 1, MID(RIGHT("000000000000000" + INT(Amount@row), 15), 3, 1) + ","), 7, 1, MID(RIGHT("000000000000000" + INT(Amount@row), 15), 6, 1) + ","), 11, 1, MID(RIGHT("000000000000000" + INT(Amount@row), 15), 9, 1) + ","), 15, 1, MID(RIGHT("000000000000000" + INT(Amount@row), 15), 12, 1) + ","), LEN(INT(Amount@row)) + (ROUNDUP(LEN(INT(Amount@row)) / 3) - 1)) + "." + MID((Amount@row - INT(Amount@row)) + "000", 3, 2)
Positive and Negative numbers with negative numbers wrapped in parenthesis:
"$12,345.90" or "($12,345.90)"
=SUBSTITUTE(SUBSTITUTE("$" + RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(RIGHT("000000000000000" + INT(Amount@row), 15), 3, 1, MID(RIGHT("000000000000000" + INT(Amount@row), 15), 3, 1) + ","), 7, 1, MID(RIGHT("000000000000000" + INT(Amount@row), 15), 6, 1) + ","), 11, 1, MID(RIGHT("000000000000000" + INT(Amount@row), 15), 9, 1) + ","), 15, 1, MID(RIGHT("000000000000000" + INT(Amount@row), 15), 12, 1) + ","), LEN(INT(Amount@row)) + (ROUNDUP(LEN(INT(Amount@row)) / 3) - 1)) + "." + MID((ABS(Amount@row) - INT(ABS(Amount@row))) + "000", 3, 2), "$-,", "($"), "$-", "($") + IF(Amount@row < 0, ")", "")
Positive and negative numbers with only a "-" before negative numbers:
"12,345.90" or "-$12,345.90"
=SUBSTITUTE(SUBSTITUTE("$" + RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(RIGHT("000000000000000" + INT(Amount@row), 15), 3, 1, MID(RIGHT("000000000000000" + INT(Amount@row), 15), 3, 1) + ","), 7, 1, MID(RIGHT("000000000000000" + INT(Amount@row), 15), 6, 1) + ","), 11, 1, MID(RIGHT("000000000000000" + INT(Amount@row), 15), 9, 1) + ","), 15, 1, MID(RIGHT("000000000000000" + INT(Amount@row), 15), 12, 1) + ","), LEN(INT(Amount@row)) + (ROUNDUP(LEN(INT(Amount@row)) / 3) - 1)) + "." + MID((ABS(Amount@row) - INT(ABS(Amount@row))) + "000", 3, 2), "$-,", "-$"), "$-", "-$")
