SharePoint 2010 Create and validate a number field in a list without thousand separator

18. February 2012 Karsten Schneider SharePoint, Tipps&Tricks 4

Unbevielable but the truth: Display a number without thousand operator and without decimal points is possible – and i did not code anything for it.

I tried to create a column which only shows an integer with six characters. This integer is a identical number for a project, for example. I checked several options till i found this smart easy no-coding solution:

  1. Create a column
  2. Column is mandatory and is primary key
  3. set maximum character to 6
  4. Insert into column validation the following formula: =ISNUMBER([Column]+0)
  5. User Dialog: Please type in a number!
  6. Save

Now you have a column, where the user can only insert numbers with a maximum of 6 characters and it will always display the number without thousand separator. I found this solution here.

If you want to go a step further and say, well – i want that the number is always six characters and only numbers are awaited, then use this formula:

=AND(LEN(Column)=6;ISNUMBER(Column+0))

It is really nice, because it also displays when your number starting with zero. The last function also validates, that you have to insert at least 6 characters.

..:: I LIKE SHAREPOINT ::..

The article or information provided here represents completely my own personal view & thought. It is recommended to test the content or scripts of the site in the lab, before making use in the production environment & use it completely at your own risk. The articles, scripts, suggestions or tricks published on the site are provided AS-IS with no warranties or guarantees and confers no rights.

Share this article

About Karsten Schneider 312 Articles
Consultant for Microsoft 365 Applications with a strong focus in Teams, SharePoint Online, OneDrive for Business as well as PowerPlatform with PowerApps, Flow and PowerBI. I provide Workshops for Governance & Security in Office 365 and Development of Solutions in the area of Collaboration and Teamwork based on Microsoft 365 and Azure Cloud Solutions. In his free time he tries to collect tipps and worthy experience in this blog.

4 Comments

  1. Your syntax for your second example is incorrect. The correct syntax is:

    EX2 (fixed):

    =AND(LEN([Column]=6),ISNUMBER([Column]+0))

    NOTE: If you are using the above code then do not use EX1 before EX2. All you need is EX2

    Thank you Karsten for the original code example though. Lead me in the right direction and I know minimal about coding!!!!

    ———————————————————————-
    ORIGINAL CODE from the author:

    EX1
    =ISNUMBER([Column]+0)

    EX2
    =AND(LEN(Column)=6;ISNUMBER(Column+0))

    Reply

    • Hi Garrett,
      thanks for commenting. I believe i used a site in german language. The difference between german and english sites is that you use a “,” in english sites and a “;” in german sites. But i am happy to lead you in the right direction. And thanks for posting your solution. I am sure it will help some other guys some day.

  2. I used this validation in Sharepoint. However, I have to update a separate column from Access to Sharepoint through a link table. Access gives me validation error on this rule as it does not recognize the syntax. Is there a solution that works for both sharepoint and Access?

    Appreciate you help.

    Reply

Leave a Reply

Your email address will not be published.


*