1

I am trying to validate a 1 to 5 position column where allowable entries are notstrict text formatted and am getting the following error:

formula contains syntax error or is not supported. 

Trying to validate a column that can contain 1-5 positions:

  • Pos 1 contains numbers
  • Pos 2 can contain Cap letters, or numbers
  • Pos 3 can contain Cap Letters or space
  • Pos 4 can contain numbers or a space
  • Pos 5 can contain Cap Letters or numbers. E.g. ( must have format of #,##,##C,##C #,#C #,#C #C)

Example of what I have been trying

=(LEN(UPC)>0)
+(LEN(UPC)<6)
+((CODE(MID(UPC,1,1))>47)
+(CODE(MID(UPC,1,1))<58)
=2)
,IF(
ISNUMBER(MID(UPC,2,1))
,((CODE(MID(UPC,2,1))>47)
+((CODE(MID(UPC,2,1))<58)
=2))
,((CODE(MID(UPC,2,1))>64)
+((CODE(MID(UPC,2,1))<91)
=2)))
,IF(
(CODE(MID(UPC,3,1))=20),
,+(CODE(MID(UPC,3,1))=20)
=1)
,((CODE(MID(UPC,3,1))>64)
+((CODE(MID(UPC,3,1))<91)
=2))
,IF(
(CODE(MID(UPC,4,1))=20),
,+(CODE(MID(UPC,4,1))=20)
=1)
,((CODE(MID(UPC,4,1))>64)
+((CODE(MID(UPC,4,1))<91)
=2))
,IF(
ISNUMBER(MID(UPC,5,1))
,((CODE(MID(UPC,5,1))>47)
+((CODE(MID(UPC,5,1))<58)
=2))
,((CODE(MID(UPC,5,1))>64)
+((CODE(MID(UPC,5,1))<91)
=2)))
1
  • mid is not a supported operator Feb 7, 2017 at 18:46

1 Answer 1

1

Eric wrote in the comments:

MID is not supported

Then I have had a very special SP since I started working with SP2003

=MID("Eric Alexander",6,4) returns Alex for me.

Eric is most likely confused with SUBSTR that does not exist in SP


All the Functions that do work in SP: https://365CSI.nl/functionlist

Yosko specifically needs to look at the AND/OR Functions to replace all those additions he now uses to fake boolean logic

((  CODE(MID(UPC,1,1))>47) + (CODE(MID(UPC,1,1))<58)  =2  )

then becomes

AND(  CODE(MID(UPC,1,1))>47 , CODE(MID(UPC,1,1))<58  )

Good thing is to built your Formulas step by step, you use CODE(MID(UPC,3,1)) 4 times.

Put it in its own Calculated Column 'Asc3'

So you can do

,IF( (Asc3=20) , ,+(Asc3=20) =1) ,((Asc3>64) +((Asc3<91) =2))

Hey! there is your failing syntax:

,+

Thinking in Strings instead of ASCII codes

Here is what you get when you think "A Letter is a Letter when it occurs in a String"

Had to dust of my old CalcMaster tool I used on projects where we did loads of maintainable Formulas..

I start with 2 Strings of Capitals and Digits and then built it step by step

Once everything works the CalcMaster tool merges all Formulas together (Blue)

And your Formula should be

=AND(
 ISNUMBER(FIND(MID(MyValue,1,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))
,ISNUMBER(FIND(MID(MyValue,2,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"&"1234567890"))
,ISNUMBER(FIND(MID(MyValue,3,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"&" "))
,ISNUMBER(FIND(MID(MyValue,4,1),"1234567890"&" "))
,ISNUMBER(FIND(MID(MyValue,5,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ" &"1234567890")))

Outputs

(Private tool) Building Calculated Formulas

screenshots shows a mix of Text/Boolean, SP is very lenient on types

Merged Formulas:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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