# Text Column Validation of 3 letters and 9 numbers

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

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.

• 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

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 "".

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