Nested If or Ifs

M_L

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".

John Jonassen

@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>

John Jonassen

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)),"")

M_L

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.

John Jonassen

@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>

M_L

Hi John,

That worked. It is keeping the # NO MATCH in both Length Left and Length Right columns.

Thank you for the help.

John Jonassen

That's great, @mlanterman!

Amanda Nelson

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.