Formula Help Needed to Remove 0 From Results
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
Best Answers
-
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.
-
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) , "")
-
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, "")
Answers
-
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
-
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),"")
-
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
-
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.
-
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.
-
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
-
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) , "")
-
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, "")
-
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
-
Happy to help! 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!
