Formula Help Needed to Remove 0 From Results

Sandra Guzman

Hello Smartsheet Community!

I need some help to better understand what is happening. Why are only some of my results generating a leading 0 in front of my text?

I have a multi-select column with a variety of text values for more than 20 groups. In the same sheet, but in different columns, I have written out a formula to pull out the specific set of values for each group. I am experiencing the same issue with all of my formulas where a leading zero is popping up for some of my results.

Here is an example of my formula:

=IF(CONTAINS("Group 1 - Position 1", Positions@row), "Group 1 - Position 1" + CHAR(10)) + IF(CONTAINS("Group 1 - Position 2", Positions@row), "Group 1 - Position 2" + CHAR(10)) + IF(CONTAINS("Group 1 - Position 3", Positions@row), "Group 1 - Position 3" + CHAR(10))

Thanks in advance!

Sandra

Toufong Vang

As suggested by @Leibel Shuchat , set the value to "" when the conditions in your IF() statements evaluate to FALSE.

My guess is that, without the blank string "", your IF() statements were evaluating to NULL until the first string is encountered (thereafter the rest of the IF() statements would evaluate to "" or blank). It seems that the sum of NULLs in Smartsheet world equals 0, a.k.a. the Boolean value of FALSE.

Toufong Vang

Two double-quotes with no space between them… ""

=IF(CONTAINS("Group 1 - Position 1", Positions@row) , "Group 1 - Position 1" + CHAR(10) , "") + IF(CONTAINS("Group 1 - Position 2", Positions@row) , "Group 1 - Position 2" + CHAR(10) , "") + IF(CONTAINS("Group 1 - Position 3", Positions@row) , "Group 1 - Position 3" + CHAR(10) , "")

Paul Newcome

Yes. You would put it in the third portion of each IF statement.

=IF(this is true, output this, "") + IF(this is true, output this, "") + IF(this is true, output this, "")

Paul Newcome

It is because you are "adding" IF statements together. When you have neither one nor two, you are basically adding two blanks together and getting a zero.

I am assuming that your screenshots contain sample data, but if that is representative of what you are wanting to accomplish, you could just use a direct cell reference to avoid that.

=Position@row

Leibel S

@Sandra Guzman

Add to the IF functions a 'value if false' (just ""). This should clear the zeros.

Example:

=IF(CONTAINS("Group 1 - Position 1", Positions@row), "Group 1 - Position 1" + CHAR(10),"")

Sandra Guzman

Hi @Paul Newcome!

The Positions column is multi-select column and includes more than 20 groups.

Ultimately there will be other values that will be selected in the "Position" column. The screenshot below should help provide a little more clarity.

The 0 only shows up for "Group 1 - Position 3" when it is the only value that is a match. This is happening to my other column formulas as well.

Is there anything I can do to get rid of that 0 from showing up?

Thanks!

Sandra

Toufong Vang

As suggested by @Leibel Shuchat , set the value to "" when the conditions in your IF() statements evaluate to FALSE.

My guess is that, without the blank string "", your IF() statements were evaluating to NULL until the first string is encountered (thereafter the rest of the IF() statements would evaluate to "" or blank). It seems that the sum of NULLs in Smartsheet world equals 0, a.k.a. the Boolean value of FALSE.

Paul Newcome

Ah. I understand more where you are going with it now. I also suggest @Leibel Shuchat's solution. That should do the trick for you.

Sandra Guzman

Hi @Leibel Shuchat - thanks for the suggestion.

So ... I tried adding " " a couple of ways and still no success.

The following formula yields #UNPARSEABLE

=IF(CONTAINS("Group 1 - Position 1", Positions@row), "Group 1 - Position 1" + CHAR(10)) + IF(CONTAINS("Group 1 - Position 2", Positions@row), "Group 1 - Position 2" + CHAR(10)) + IF(CONTAINS("Group 1 - Position 3", Positions@row), "Group 1 - Position 3" + CHAR(10)), " ")

Adding IF ERROR to the formula and I still get the 0 as before.

=IFERROR(IF(CONTAINS("Group 1 - Position 1", Positions@row), "Group 1 - Position 1" + CHAR(10)) + IF(CONTAINS("Group 1 - Position 2", Positions@row), "Group 1 - Position 2" + CHAR(10)) + IF(CONTAINS("Group 1 - Position 3", Positions@row), "Group 1 - Position 3" + CHAR(10)), " ")

I have tried clearing contents and adding an entirely new column ...but it appears that I still get those 0's.

Sandra

Toufong Vang

Two double-quotes with no space between them… ""

=IF(CONTAINS("Group 1 - Position 1", Positions@row) , "Group 1 - Position 1" + CHAR(10) , "") + IF(CONTAINS("Group 1 - Position 2", Positions@row) , "Group 1 - Position 2" + CHAR(10) , "") + IF(CONTAINS("Group 1 - Position 3", Positions@row) , "Group 1 - Position 3" + CHAR(10) , "")

Paul Newcome

Yes. You would put it in the third portion of each IF statement.

=IF(this is true, output this, "") + IF(this is true, output this, "") + IF(this is true, output this, "")

Sandra Guzman

To @Paul Newcome, @Leibel Shuchat & @Toufong Vang,

Thank you so much for your explanation and help. I now understand why the zero is populating and see how my adding the "" to the end of my formula was still not going to achieve the results that I needed. Adding it to the third portion of my IF statement did the trick. I also think this all clicked this morning and makes complete sense. Maybe I just needed a little more coffee...

I love our Smartsheet Community! So many brilliant minds out there.

Thanks again!

Sandra

Paul Newcome

Happy to help! 👍️