Join formula started adding a leading zero on text from certain columns

Terri-Lynn Morrison · 04/30/24

I created this formula, and it worked great the first two times it was used. The third time, it added a leading 0, which is NOT present in the [Circulation Requirement Details 3] column. I did further testing triggering the function using [Circulation Requirement Details 4] and [Circulation Requirement Details 5], and 0 is also present. When I trigger the formula referencing [Circulation Requirement Details 1] or [Circulation Requirement Details 2], the 0 is not present.

Formula =IF(NOT(ISBLANK([Circulation Requirement]@row)), JOIN([Circulation Requirement Details 1]@row + [Circulation Requirement Details 2]@row + [Circulation Requirement Details 3]@row + [Circulation Requirement Details 4]@row + [Circulation Requirement Details 5]@row, ""), "")

The columns are filled using default values in a form as they were too long to be drop-down values.

I have a redacted sheet I can share if needed. Otherwise, I have provided screenshots. https://app.smartsheet.com/sheets/vxcCFqh5GPcWffhPMXfQ9Crf4v6h3h76PVqq4rj1

Form - Default text 1-3.jpg

Form - Default text 4-5.jpg

Sheet showing text and column formula.jpg

Andrée Starå · 04/30/24

https://community.smartsheet.com/discussion/121299/join-formula-started-adding-a-leading-zero-on-text-from-certain-columns

Hi,

I hope you're well and safe!

I'd recommend using a JOIN COLLECT structure instead.

Try something like this. (update the columns to match yours)

=JOIN(COLLECT([Key Accomplishments]:[Key Accomplishments], [Key Accomplishments]:[Key Accomplishments], @cell <> "", [Pull Into Report?]:[Pull Into Report?], @cell = 1), CHAR(10))

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Terri-Lynn Morrison · 05/01/24

I have been trying a Join Connect formula for the last three hours. I can not make it row-specific, and it consistently gathers any text from those 5 columns regardless of what row the text is in.

Andrée Starå · 05/01/24

https://community.smartsheet.com/discussion/comment/434023#Comment_434023

Can you paste the formula you're testing?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Terri-Lynn Morrison · 05/01/24

https://community.smartsheet.com/discussion/comment/434117#Comment_434117

I tried so many variations, but the only one that shows anything besides an error is:

=JOIN(COLLECT([Circulation Requirement Details 1]:[Circulation Requirement Details 5], [Circulation Requirement Details 1]:[Circulation Requirement Details 5], @cell <> ""), ", ")

Terri-Lynn Morrison · 05/01/24

https://community.smartsheet.com/discussion/comment/434142#Comment_434142

I was able to make this formula work, but I still wish I knew why my original formula started adding 0.

=IF(AND(NOT(ISBLANK([Circulation Requirement]@row)), CONTAINS("Surplus to municipal needs & tech. conditions (for sale purposes)", [Circulation Requirement]@row)), [Circulation Requirement Details 1]@row, IF(CONTAINS("Technical Conditions Only (for sale purposes)", [Circulation Requirement]@row), [Circulation Requirement Details 2]@row, IF(CONTAINS("Surplus to municipal needs & tech. conditions (for lease purposes)", [Circulation Requirement]@row), [Circulation Requirement Details 3]@row, IF(CONTAINS("Surplus to municipal needs & tech. conditions (for lease purposes - lease extension)", [Circulation Requirement]@row), [Circulation Requirement Details 4]@row, IF(CONTAINS("Surplus to municipal needs & tech. conditions (for lease purposes - lease renewal)", [Circulation Requirement]@row), [Circulation Requirement Details 5]@row, "")))))

Join formula started adding a leading zero on text from certain columns

Answers

Help Article Resources

Categories