SOLVED

Set a minimum number of characters for a SharePoint list Column

Copper Contributor

Hi everyone, 

 

So I have a SharePoint list with many columns one of them being the social security number.

 

In order to minimize errors, I would like for people to be able to put 15 characters only. I limited the number of characters to 15 max, and I did some research and tried to put the minimum to 15 through validation but it is not working and I can't figure out why (I did try multiple solutions and spelling).

 

On the screenshot you can see the validation box, an error message about wrong syntax keeps appearing. 

 

How can I set a minimum number of characters for a column ?

 

Thank  you for your help !

Selma

9 Replies

@SelmaA 

So far I know, you can set a minimum number of characters for a SharePoint list column by using column validation.

In the column settings, under the “Column Validation” section, you can enter a formula to validate the input.

For example, to set a minimum of 15 characters for a single line of text column, you can use the formula =LEN([Column Name])>=15.

Replace [Column Name] with the actual name of your column.

Make sure that the formula is entered correctly and that there are no spelling errors.

 

I hope that I could help with that!

@SelmaA Are you trying to have the exact 15 characters in the column? If yes, then use formula like: 

 

=IF(ISBLANK([column 1]), TRUE, IF(LEN([column 1])=15, TRUE, FALSE)

 

Use correct display name of your column. Also, as you are using non-English language try using LEN function equivalent in your language.

 

Note: Sometimes comma( , ) does not work in formula (it is based on language or regional settings on your site). So in that case use semicolon( ; ) instead of comma( , ).


Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs

Thank you for your reply ! This is what I tried and it is not working and I can't figure out why.
I'm glad I could help.
I wish you continued success with SharePoint!
Thank you for your reply ! I did try it and still get the same syntax error message (I also tried with semicolon instead of comma and also by using the function equivalent in french).
Could it be that it is not working because I already have data on that column ?

@SelmaA Here are some similar threads for reference:

  1. How to set the minimum length of a single line text in SharePoint online? 
  2. Column validation on a non required field 

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs

best response confirmed by SelmaA (Copper Contributor)
Solution

@SelmaA Can you try using List Validation settings instead of column validation?

 

Also, try using same formula on different list (without data) in same SharePoint site and different SharePoint site (with English language) and see if it is working for you.


Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs

Thank you !

I tried using list Validation instead of column validation, it still doesn't work but I am able so save my formula and don't get any error message (the functions are then automatically changed to the french equivalent) however when adding a new item to the list nothing happens, I am still able to put more than 15 characters.
I created a new list and tried again your formula, it now works, the item cannot be saved if this column is more or less than 15 characters, however the error message that appears is not the one that I wrote but rather a system message.
I kinda found a solution to my issue.
When creating the new SharePoint list, I tried adding a new column and chose number instead of text. I then set the minimum to 100000000000000 and max to 999999999999999 (since our social security numbers start by 1 or 2) and it seems to be working for now. I will still try to figure out why your formula is saved and working but the message not appearing. Thank you very much !

@SelmaA It maybe showing error message something like this: Error: The server was unable to save the form at this time. Please try again.

 

This is a issue/bug from Microsoft's end as other SharePoint users are also facing this issue while using validations in SharePoint lists.

 

Here's related thread where you can find more information: Microsoft List validation not working in SharePoint Online - Error: The server was unable to save th... 


Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs

1 best response

Accepted Solutions
best response confirmed by SelmaA (Copper Contributor)
Solution

@SelmaA Can you try using List Validation settings instead of column validation?

 

Also, try using same formula on different list (without data) in same SharePoint site and different SharePoint site (with English language) and see if it is working for you.


Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs

View solution in original post