0

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
2

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
2

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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.