# 2010: Calculated column multiple if statements

Currently, I have a formula to execute some number stripping for a column. When the user uses a card swipe, it outputs something like: ;502780133=0444?

The formula strips the semicolon and removes everything from the equal sign and on. Also, if a number isn't entered, it will display the error message "Enter SUID on Package Pickup"

``````=IF(ISERROR(RIGHT(LEFT(SUID,INT(FIND("=",SUID)-1)),LEN(LEFT(SUID,INT(FIND("=",SUID)-1)))-1)),"Enter the SUID on Package Pickup",RIGHT(LEFT(SUID,INT(FIND("=",SUID)-1)),LEN(LEFT(SUID,INT(FIND("=",SUID)-1)))-1))
``````

I want to keep the error message intact, but I want the number stripping to occur only if a semicolon is found. This will help prevent errors when users manually enter the ID numbers.

Is this possible? If so, what would that formula look like?

• Nicole, Have you tried adding another IF statement to check for the semicolon first? – jpollar Feb 11 '15 at 18:23
• That's what I'm looking to do, but with my beginner knowledge of calculated column formulas, I'm not sure how to do that within my current formula. – nicolefdesigns Feb 11 '15 at 18:46
• Okay. Let me give it a shot. Will get back to you shortly. – jpollar Feb 11 '15 at 18:57
• Nice site btw... – jpollar Feb 11 '15 at 19:25

It is easier if you build your Formula in partials/multiple Calculated Formulas, you can add a maximum of 48 Calculated Columns to a List and you don't have to use everyone in a View or Form.

So create a separate Column "NumberStripping" with you Formula

``````RIGHT(LEFT(SUID,FIND("=",SUID)-1),LEN(LEFT(SUID,FIND("=",SUID)-1))-1)
``````

That is the part you want to wrap in an IF

``````IF( LEFT(SUID,1)=";" , [NumberStripping] , [NoNumberStripping] )
``````

You will end up with multiple Columns, but much easier to read and debug; the number of Columns hardly effect performance

Note you can hide Calcuted Columns on Edit/New/Display Forms by not adding them to the default ContentType of a List; OR use SharePoint Designer to hide the Column; see http://viewmaster365.com/365coach/#/Blog_hidecolumn

• Thank you for your answer. I just need a bit more of explanation. I put the IF statement in a column called SUID Number. I created NumberStripping with my original formula and NoNumberStripping as a normal single line of text column. When I go to save it says a formula is referring to a column that does not exist. Any idea why this may be happening? – nicolefdesigns Feb 12 '15 at 14:32
• If you created a Column [SUID Number] you need to use that displayname in other formulas. Those square brackets are required for Column names with spaces in them; not required for Columns that have no spaces – Danny '365CSI' Engelman Feb 12 '15 at 17:35
• Sorry for my confusion. I just want to better understand your answer. I created a calculated column called SUID Number that has `IF( LEFT(SUID,1)=";" , [NumberStripping] , [NoNumberStripping] )` so it knows that when a semicolon is found, it will execute the NumberStripping calculated column with the formula `RIGHT(LEFT(SUID,FIND("=",SUID)-1),LEN(LEFT(SUID,FIND("=",SUID)-1))-1)`. Then I created NoNumberStripping as a regular single text column. The IF statement in SUID Number is pointing two valid columns. I'm not sure what I'm missing. – nicolefdesigns Feb 12 '15 at 18:06
• If [SUID Number] references [NoNumberStripping] you will have to create that Column first, otherwise you get the Column does not exist error. – Danny '365CSI' Engelman Feb 13 '15 at 18:49

Nicole,

Danny's solution should also work.

If you want to keep it in one formula, this should work.

``````=IF(LEFT(SUID,1)=";",IF(ISERROR(RIGHT(LEFT(SUID,INT(FIND("=",SUID)-1)),LEN(LEFT(SUID,INT(FIND("=",SUID)-1)))-1)),"Enter the SUID on Package Pickup",RIGHT(LEFT(SUID,INT(FIND("=",SUID)-1)),LEN(LEFT(SUID,INT(FIND("=",SUID)-1)))-1)),"Enter the SUID on Package Pickup")
``````
• Unfortunately this did not do the trick. When I enter in the numbers manually, it shows "Enter the SUID on Package Pickup". I only want that to appear when the column has no values in it. – nicolefdesigns Feb 12 '15 at 14:19
• So you want it to disappear once they start typing? – jpollar Feb 12 '15 at 19:13
• Try this. =IF(LEFT(SUID,1)=";",IF(ISERROR(RIGHT(LEFT(SUID,INT(FIND("=",SUID)-1)),LEN(LEFT(SUID,INT(FIND("=",SUID)-1)))-1)),"Enter the SUID on Package Pickup",RIGHT(LEFT(SUID,INT(FIND("=",SUID)-1)),LEN(LEFT(SUID,INT(FIND("=",SUID)-1)))-1)),SUID) – jpollar Feb 12 '15 at 19:23

Got some internal help and produced this answer:

``````=IF(
LEFT(SUID,1)=";"
,MID(SUID,2,9)
,IF(LEN(TRIM(SUID))=0
,"Enter SUID on Pickup"
,IF(AND(LEN(TRIM(SUID))>0
,LEN(TRIM(SUID))<9)
,"Invalid SUID",SUID
)
)
)
``````