Sharepoint Lists column validation syntax

Question

Hi

I am looking for a syntax, to check that column content is 5 digit in lenght and content is all numbers.

For example, 00100 is accepted, 0010 is not accepted.

I have troubles to find syntax, that would refer to the column content. When my column name is TestColumn and If I test with

=ISNUMBER(TestColumn)

it tests whether text TestColumn is a number. Obviously it is not so validation gives FALSE. It does not test whether the content in the column, named TestColumn, is a number or not.

If I try with

=ISNUMBER([TestColumn])

and save the column edit, it just removes the brackets and I end up in the situation in the first code example.

What would be the correct syntax to check, whether the content in column, named TestColumn, is only numbers or not?

I have the same kind of problem with the content lenght syntax. I have tried

=IF(LEN(TestColumn)=5

save the column setting edit and open the settings again, syntax has been removed from the validation field.

I just can't figure out what I am doing wrong..??

Answer 1

Please use following formula in the list validation.

=IF(LEN(TestColumn)=5,IF(ISNUMBER(TestColumn+0),TRUE,FALSE),FALSE)

Result:
1.If the column contains letter,

2.If the column contains 6 number,

3.If the column contains 5 number,

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thank you! This worked.