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

Tags:

• ✭✭✭✭
edited 08/26/20

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.

• ✭✭✭✭✭

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.