SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It only takes a minute to sign up.
Sign up to join this community
I'm pretty new to this and I can't find any specific example on how to validate the lenght of a field (12) The first 3 positions are letters and the next 9 numbers. For example: ABC123456789. They don't have to be capital letters.
I tried this by piecing together a few different things but I'm honestly totally lost. AND(LEN(ColumnName)=12, LEFT(ColumnName,3)=[A-Z], ISNUMBER(0+MID(ColumnName,4,9)))
Your help is appreciated!
The field validation formulas are more or less excel functions. So, I would suggest you to try your formulas in excel first.
you will probably end up with something like this (assuming your data are in cell B1):
=AND(LEN(B1)=12, ISTEXT(LEFT(B1,3)), ISNUMBER(0+MID(B1,4,9)))
(It seems that the "ISTEXT" in SP is not behaving as expected. helpfull articles about these functions can be found here. and here)
And of course you need to replace B1 with your column name.
good luck,
d.
You could use the following formula:
=IF(AND(LEN(Test)=12,OR(AND(CODE(MID(Test,1,1))>96,CODE(MID(Test,1,1))<123),AND(CODE(MID(Test,1,1))>64,CODE(MID(Test,1,1))<91)),OR(AND(CODE(MID(Test,2,1))>96,CODE(MID(Test,2,1))<123),AND(CODE(MID(Test,2,1))>64,CODE(MID(Test,2,1))<91)),OR(AND(CODE(MID(Test,3,1))>96,CODE(MID(Test,3,1))<123),AND(CODE(MID(Test,3,1))>64,CODE(MID(Test,3,1))<91)),CODE(MID(Test,4,1))>47,CODE(MID(Test,4,1))<58,CODE(MID(Test,5,1))>47,CODE(MID(Test,5,1))<58,CODE(MID(Test,6,1))>47,CODE(MID(Test,6,1))<58,CODE(MID(Test,7,1))>47,CODE(MID(Test,7,1))<58,CODE(MID(Test,8,1))>47,CODE(MID(Test,8,1))<58,CODE(MID(Test,9,1))>47,CODE(MID(Test,9,1))<58,CODE(MID(Test,10,1))>47,CODE(MID(Test,10,1))<58,CODE(MID(Test,11,1))>47,CODE(MID(Test,11,1))<58,CODE(MID(Test,12,1))>47,CODE(MID(Test,12,1))<58),TRUE,FALSE)
As a result, the validation runs normally:
Notice:Per my test, if we use "12" as the column name, we need to replace "Test" in the formula with "[12]".
If the answer is helpful, please click "√" on the left panel of the answer and kindly upvote it.
Thank you both for your help in solving this puzzle. I used both of your examples to help me piece this together.
=AND(LEN([ColumnName])=12,AND(LEFT([ColumnName],1)>="a",LEFT([ColumnName],1)<="z",AND(MID([ColumnName],2,1)>="a",MID([ColumnName],2,1)<="z",AND(MID([ColumnName],3,1)>="a",MID([ColumnName],3,1)<="z",ISNUMBER(0+MID([ColumnName],4,9))))))
This example verifies that the column has 12 characters, that positions 1, 2, 3 are each within A-Z and that the 4-9 Are numbers.
I learned a lot during this process. The links to articles were very helpful.
