JOIN() Formula Result - Producing Apostrophe in Copied Cell Data

Adriane Price

The issue involves the usage of the JOIN() formula within SmartSheet, which is resulting in the inclusion of an unwanted apostrophe when the formula's output is copied to another sheet. The JOIN() function is designed to concatenate values, but in this scenario, the copied data is displaying an apostrophe that is not intended to be present. This issue is affecting the accuracy and formatting of the data when it is transferred between sheets, causing confusion and errors in data interpretation. I would like to resolve this problem so that the copied data retains its original format [just numbers] and does not introduce any extraneous characters like the unintended apostrophe.

I do not know how to fix this issue, I have checked formatting in the columns and there is no formatting, including the intake form it is selected to "number" only. I also have a ticket open with Smartsheet support but thought I would reach out to the community too.

In case anyone has ran into this issue before I would appreciate the insight or guidance, please.

Adriane Price

Fixed, needed to add "Value" before the formula to remove the apostrophe.

=VALUE(IFERROR(JOIN(DISTINCT(COLLECT([Customer PO Amount (USD)]@row:[Customer PO Amount (Local Currency)]@row, [Customer PO Amount (USD)]@row:[Customer PO Amount (Local Currency)]@row, <>""))), " "))

Adriane Price

Fixed, needed to add "Value" before the formula to remove the apostrophe.

=VALUE(IFERROR(JOIN(DISTINCT(COLLECT([Customer PO Amount (USD)]@row:[Customer PO Amount (Local Currency)]@row, [Customer PO Amount (USD)]@row:[Customer PO Amount (Local Currency)]@row, <>""))), " "))