..:: I like SharePoint ::.. Rotating Header Image

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

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.

Karsten Pohnke About Karsten Pohnke
He is Consultant for SharePoint Solutions for collaboration, communication and business processes. He provides his customers applications based on standard features as well as development or combining the power of several microsoft tools like Dynamics CRM. In his free time he tries to collect tipps and worthy experience in this blog.

4 Comments

  1. Garrett says:

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

    1. 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. Nikesh says:

    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.

    1. i am sorry, i never tested with access. So you can’t update the column by using SharePoint?

Leave a Reply

Your email address will not be published. Required fields are marked *


one × three =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>