Concatenate Displays "0" for Blank....But Not Always
I am concatenating fields (only one is filled per row) so that I can use that result in the calendar view. I added a new field to the concatenation ([column one name]@row + [column two name]@row....) this morning and the new row is pushing a "0" (zero) into the concatenation when blank. None of the other blank fields is pushing a zero. All fields used in the concatenation are formatted as Dropdown (single select).
Here is the concatenation formula I am using: =[Ajera Pod]@row + [ConceptShare Pod]@row + [Costpoint Pod]@row + [Talent DLZ Pod]@row + [Talent DTM Pod]@row + [Vantagepoint Pod]@row + [Vision Pod]@row
The column "Ajera Pod" is the one pushing the 0 into the display field. This is occurring in every row.
What are my troubleshooting steps?
Best Answer

Use JOIN instead. JOIN acts as if any value, even blanks, are text and not numbers. Your formula above is trying to add all the blanks together as possible integers. I know, it's weird. The below will leave a blank cell if all the input cells are blank:
=JOIN([Ajera Pod]@row:[Vision Pod]@row)
This assumes your columns are all in order left to right and that's how you want them displayed. You can add a delimiter between the values as well:
=JOIN([Ajera Pod]@row:[Vision Pod]@row, "  ")
If you need them in a different order, then use + in between the field names with JOIN:
=JOIN([Ajera Pod]@row + [ConceptShare Pod]@row + [Costpoint Pod]@row + [Talent DLZ Pod]@row + [Talent DTM Pod]@row + [Vantagepoint Pod]@row + [Vision Pod]@row)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers

Also  0 is not a valid entry in any of the columns.

Use JOIN instead. JOIN acts as if any value, even blanks, are text and not numbers. Your formula above is trying to add all the blanks together as possible integers. I know, it's weird. The below will leave a blank cell if all the input cells are blank:
=JOIN([Ajera Pod]@row:[Vision Pod]@row)
This assumes your columns are all in order left to right and that's how you want them displayed. You can add a delimiter between the values as well:
=JOIN([Ajera Pod]@row:[Vision Pod]@row, "  ")
If you need them in a different order, then use + in between the field names with JOIN:
=JOIN([Ajera Pod]@row + [ConceptShare Pod]@row + [Costpoint Pod]@row + [Talent DLZ Pod]@row + [Talent DTM Pod]@row + [Vantagepoint Pod]@row + [Vision Pod]@row)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

@JReisman27: Interesting result. I edited the formula as you recommended and got an error. Then I copied/pasted the formula as you typed it above. I also removed the first column (the one that has been giving me trouble). All of those attempts result in #INCORRECT ARGUMENT SET.
I need to look into the JOIN command more closely.

AHA!
=JOIN([First Column]:[Last Column])
....is the correct formula to use.

@Steve Kopischke  Interesting. It works fine for me:
These are singleselect fields with various data types in the choices.
Clicking into my formula field shows it's targeting the correct cells:
What does yours show?
I have encountered glitches where Smartsheet does not pick up the correct targeting until I type the formula by hand and let it create its own pointers to the referenced cells as I go.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

#INCORRECT ARGUMENT SET
Cause
This error is presented under the following circumstances:
 For functions that take two ranges: The range sizes don’t match for the function.
 The function is missing an argument.
 There is an extra function in the argument.
Resolution
Correct the range size or arguments, adding or removing arguments in the formula.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

The original formula was working fine until I added the "Ajera Pod" column this morning. If I remove the column, the formula works.
When I changed from the original to JOIN, I edited the formula with the keyboard, then copy/paste. If I removed the new column, it still wouldn't work. It works with the first and last columns bracketed by the ":" character.

OK  try this one:
=JOIN([Ajera Pod]@row) + JOIN([ConceptShare Pod]@row) + JOIN([Costpoint Pod]@row) + JOIN([Talent DLZ Pod]@row) + JOIN([Talent DTM Pod]@row) + JOIN([Vantagepoint Pod]@row) + JOIN([Vision Pod]@row)
The individual JOINs turn each cell into text.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

@JReisman27 I have been looking for this exact solution! Thank you! 🏅
Help Article Resources
Categories
Check out the Formula Handbook template!