Nested If or Ifs
Hello,
I have one cell with two pieces of data separated by a / but not all cells have two two data points.
I have a formula to separate out the data to the left and right of the / when there are two pieces of data, but am struggling to find a way around the error of "Invalid Value" when there is only one piece of data in the cell.
Length Left Formula:
=LEFT(Length62, FIND("/", Length62)  1)
Length Right Formula:
=RIGHT(Length62, LEN(Length62)  FIND("/", Length62))
I think I might need a nested If statement? I am not able to find the right syntax to get it to return in all cases.
Something like " If Box Type is Inner, return Length, If Box Type is Master, return Length, and if Inner & Master, return Length Left".
Best Answer

@mlanterman Yes, that is currently in the suggested Length Right Formula I provided in my previous reply. So when you have a single value with no slashes, it will always be in the Length Right.
As for keeping the #NO MATCH, I'm not 100% sure and can't quite recreate how to get a #NO MATCH, but try
Length Left Formula:
=IF(Length62="#NO MATCH","#NO MATCH",IFERROR(LEFT(Length62, FIND("/", Length62)  1),""))
Length Right Formula:
=IF(Length62="#NO MATCH","#NO MATCH",IFERROR(RIGHT(Length62, LEN(Length62)  FIND("/", Length62)),""))
<fingers crossed>
Answers

It's because of each of the formulas looking /. Simple fix. Both will return a null value when appropriate.
Length Left Formula:
=IFERROR(LEFT(Length62, FIND("/", Length62)  1),"")
Length Right Formula:
=IFERROR(RIGHT(Length62, LEN(Length62)  FIND("/", Length62)),"")

Hi John,
Thanks for the tip.
I would still like to have the 6, or a single value returned when there is no /, is that possible? And we want to keep the #NO MATCH, as that is used in another auditing process we have.

@mlanterman Yes, that is currently in the suggested Length Right Formula I provided in my previous reply. So when you have a single value with no slashes, it will always be in the Length Right.
As for keeping the #NO MATCH, I'm not 100% sure and can't quite recreate how to get a #NO MATCH, but try
Length Left Formula:
=IF(Length62="#NO MATCH","#NO MATCH",IFERROR(LEFT(Length62, FIND("/", Length62)  1),""))
Length Right Formula:
=IF(Length62="#NO MATCH","#NO MATCH",IFERROR(RIGHT(Length62, LEN(Length62)  FIND("/", Length62)),""))
<fingers crossed>

Hi John,
That worked. It is keeping the # NO MATCH in both Length Left and Length Right columns.
Thank you for the help.

That's great, @mlanterman!

I am new to SmartSheet and I need help. I have my formula from excel that works and gives me what I need.
=IF(OR(T3731<>" "),LEFT(T3731,3))& IF(OR(U3731<>" "),LEFT(U3731,3))&IF(OR(V3731<>" "),LEFT(V3731,3))
Basically, I want to look at three different columns. If the column is not blank then I want to pull the left three values of that cell. If the cell is blank then I want to look at column 2 and if not blank then the left three values of that cell and then repeat again.
This is what I have so far in SmartSheet:
=if(or(MemberError1<>" "),left(MemberError1,3))&(if(facilityerror1<>" "),left(facilityerror1,3))&(if(serviceerror1<>" "),left(serviceerror1,3))
I keep getting the unparsed error and do not know what I am missing.
Please help.
Help Article Resources
Categories
Check out the Formula Handbook template!