0

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!

3 Answers 3

0

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.

1
  • Thank you for this solution it works to ensure that they don't add special characters but it still doesn't let me verify to ensure that the first 3 characters are only letters. But for now I will most definately use this to help limit the data. Jun 2 at 21:29
0

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: enter image description here

enter image description here

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.

2
  • Thank you for the reply but it apperas I got lost in this statement. Where in the statement do we test to ensure that position 1,2,3 are only letters? Jun 3 at 12:54
  • Use this formula: =IF(AND(LEN(Test)=1,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))),TRUE,FALSE), you could ensure that the position 1 is only letter, and the position 2,3 are same.
    – Yi Lu_MSFT
    Jun 6 at 1:17
0

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.

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.