Question about currency formatting

Murz

Hi,

I have a helper column I am using to insert text into an email alert (the 2000 characters is not enough for my needs so I'm using several formulas to insert the data). Anyway, one of my columns is my "Header text" for the email and it includes the name of the person making the transaction, the transaction total, date of purchase, vendor, etc.

My issue is that the transaction total appears in my formula as the number, with no currency formatting (except for the decimal place). In the column, I have it formatted correctly using the column formatting functionality and it appears correctly in the column and in other places like reports.

Right now, I'm including a text "$" in front of the value in my formula but I can't figure out how to include the comma if the number is 1,000 or above.

This is my formula.

=JOIN("Hi Brian" + CHAR(10) + CHAR(10) + [Cardholder_First]@row + " " + [Cardholder_Last]@row) + " " + "has made a P-Card Transaction which needs your review." + CHAR(10) + CHAR(10) + "TRANSACTION TOTAL: " + "$" + [Transaction (Receipt) Total]@row + CHAR(10) + "DATE OF PURCHASE: " + [Date of Purchase]@row + CHAR(10) + "VENDOR: " + Vendor@row + CHAR(10) + "DATE OF PURCHASE: " + [Date of Purchase]@row

The [Transaction (Receipt) Total] is the value that I'm working with. It is input into the original form as an unformatted number, with only a decimal place for the cents. For example, an entry of $1,000.00 would be entered in as 1000.00

Is there a way to use a formula, maybe in a helper column, to format the number so it shows in my formula above as $1,000.00? As you can see, all I have is the "$", which works fine with numbers 999.99 and less. I am guessing its not a critical issue but for aesthetics, I'd like to see if i can resolve it.

This is what generates as part of the formula.

Thank you so much in advance, I appreciate all your thoughts and help!

jmyzk_cloudsmart_jp

Hi @Murz

You're right that Smartsheet doesn't provide native number formatting (like commas for thousands) within formulas. You're currently adding a dollar sign ("$") manually, which works for values under 1,000 — but it doesn’t add commas for larger numbers.

In my solution, I handle this by treating the number as text. I convert the integer portion of the number to a string using "" + INT(...), and then use LEFT, MID, and RIGHT to manually insert commas based on the number of digits.

The decimal part is also handled as text using ROUND(... * 100, 0) to extract cents, and then padded using RIGHT("00" + ..., 2).

It’s a bit manual, but this lets you generate a string like $210,000,452.25 without using TEXT(), which isn’t available in Smartsheet.

https://app.smartsheet.com/b/publish?EQBCT=8b40fd67ff0341a3a5afc073a5eb9da2

="$" +

IF(LEN(INT([Transaction (Receipt) Total]@row)) = 9,

    LEFT(INT([Transaction (Receipt) Total]@row),3) + "," +

    MID(INT([Transaction (Receipt) Total]@row),4,3) + "," +

    RIGHT(INT([Transaction (Receipt) Total]@row),3),

IF(LEN(INT([Transaction (Receipt) Total]@row)) = 8,

    LEFT(INT([Transaction (Receipt) Total]@row),2) + "," +

    MID(INT([Transaction (Receipt) Total]@row),3,3) + "," +

    RIGHT(INT([Transaction (Receipt) Total]@row),3),

IF(LEN(INT([Transaction (Receipt) Total]@row)) = 7,

    LEFT(INT([Transaction (Receipt) Total]@row),1) + "," +

    MID(INT([Transaction (Receipt) Total]@row),2,3) + "," +

    RIGHT(INT([Transaction (Receipt) Total]@row),3),

IF(LEN(INT([Transaction (Receipt) Total]@row)) = 6,

    LEFT(INT([Transaction (Receipt) Total]@row),3) + "," +

    RIGHT(INT([Transaction (Receipt) Total]@row),3),

IF(LEN(INT([Transaction (Receipt) Total]@row)) = 5,

    LEFT(INT([Transaction (Receipt) Total]@row),2) + "," +

    RIGHT(INT([Transaction (Receipt) Total]@row),3),

IF(LEN(INT([Transaction (Receipt) Total]@row)) = 4,

    LEFT(INT([Transaction (Receipt) Total]@row),1) + "," +

    RIGHT(INT([Transaction (Receipt) Total]@row),3),

INT([Transaction (Receipt) Total]@row)

)))))) +

"." +

RIGHT("00" + ROUND(([Transaction (Receipt) Total]@row - INT([Transaction (Receipt) Total]@row)) * 100, 0), 2)