Formatting Values with Join Collect

J. Angel

Hello!

I had used a join/collect formula to pull the data from another reference sheet. I need help formatting the money and the percentages.

Here are the formulas I used:

=IFERROR(IF(AND($[Primary Column]@row = "Include", COUNTIF({ACT_Vendor Name}, $Vendor@row ) > 1), "$" + JOIN(COLLECT({ACT_Total Contract}, {ACT_Vendor Name}, $Vendor@row ), CHAR(10) + CHAR(10) + "$"), INDEX({ACT_Total Contract}, MATCH($Vendor@row , {ACT_Vendor Name}, 0))), "")

=IFERROR(IF(AND($[Primary Column]@row = "Include", COUNTIF({ACT_Vendor Name}, $Vendor@row ) > 1), JOIN(COLLECT({ACT_Consumption}, {ACT_Vendor Name}, $Vendor@row ), CHAR(10) + CHAR(10)), INDEX({ACT_Consumption}, MATCH($Vendor@row , {ACT_Vendor Name}, 0))), "")

jmyzk_cloudsmart_jp

Hi @J. Angel

When using JOIN(COLLECT()) to display multiple values in a single cell with proper formatting, the cleanest approach is to format the values first in helper columns, and then join those formatted results.

Smartsheet doesn’t allow number formatting inside JOIN, so formatting needs to happen before the join.

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

Currency Formatting ($234,222,212.0)

Since Smartsheet doesn’t support TEXT() formatting like Excel, I created a helper column to manually format the number with:

• Commas every 3 digits
• A leading $
• Always one decimal place (.0)

The logic works by:

1. Getting the digit length using LEN
2. Using MOD(LEN(value), 3) to determine the first digit group
3. Rebuilding the number using LEFT, MID, and RIGHT

Example helper column formula:

="$" +

IF(

    LEN([ACT_Total Contract]@row) <= 3,

    [ACT_Total Contract]@row,


    IF(MOD(LEN([ACT_Total Contract]@row), 3) = 0,

        LEFT([ACT_Total Contract]@row, 3),

        LEFT([ACT_Total Contract]@row, MOD(LEN([ACT_Total Contract]@row), 3))

    )


    +


    IF(LEN([ACT_Total Contract]@row) > 3,

        "," + MID(

            [ACT_Total Contract]@row,

            IF(MOD(LEN([ACT_Total Contract]@row), 3) = 0, 4, MOD(LEN([ACT_Total Contract]@row), 3) + 1),

            3

        ),

        ""

    )


    +


    IF(LEN([ACT_Total Contract]@row) > 6,

        "," + RIGHT([ACT_Total Contract]@row, 3),

        ""

    )

)

+ ".0"

Example result:

234222212  →  $234,222,212.0

The key idea is that MOD(length,3) tells us how many digits should appear before the first comma.

Percentage Formatting

For percentages, I created a helper column using:

=INT([ACT_Consumption]@row  * 100 + 0.5) + "%"

This:

• Converts decimal to percent
• Rounds properly
• Appends %
• Works cleanly with JOIN

Joining Multiple Formatted Values

Once formatting is handled in helper columns, the JOIN becomes simple and clean:

=IF(
    [Include & multiple Vendor names]@row,
    JOIN(
        COLLECT({$ACT_Total Contract}, {ACT_Vendor Name}, Vendor@row),
        CHAR(10)
    )
)

The same approach applies to the formatted % helper column.

https://app.smartsheet.com/b/publish?EQBCT=873546d1e90c463cb9325664a4c7bddb

Why This Works Best

• Keeps formatting logic separate from aggregation logic
• Makes formulas much easier to read and maintain
• Prevents extremely long nested formulas
• Performs better in larger sheets

In short:

Format first → then JOIN.

Hope that helps!