Hello SharePointers,
Today in this article, we will learn to validate phone number columns in SharePoint Online.

In our day to day SharePoint development life we need to work on various aspects of technology. Also, we face too many technical challenges as a developer.

Context

The Same challenge I faced during recent working on SharePoint Online List. As you all know, the SharePoint Number field (Telephone No.) always store comma-separated values as shown in the below image.

Validate phone number column in SharePoint
Validate phone number column in SharePoint

But our client requirement was to display a Telephone Number in the United States (US) writing format, i.e. (308) 555-4444 [Whose number is this?]

The basic requirement is like,
1) Users can enter a Telephone number in a standard way like 1234567890.
2) But at the backend, in list view, Telephone Number should look like (123) 456-7890

Solution

We can achieve this by multiple ways. Please go through each step below to fulfill your requirement.

The best and very speedy as well as very easy way to achieve our target is by using Calculated columns.

Follow below steps to create calculated column.

  1. Go to List where you want to validate the Telephone Number column in SharePoint.
  2. Click on Gear icon from Top right corner and Select List Settings
Validate phone number column in SharePoint
Validate phone number column in SharePoint
  1. Then Click on Add new Column and give a name like “Telephone Num”.
  2. Type of Column must be Calculated (calculation based on other columns)
  3. Add a formula =TEXT([Telephone No.],”(000) 000-0000″)
  4. click on OK.
Validate phone number column in SharePoint
Validate phone number column in SharePoint

This will display a general number column in the United States telephone number format.

Validate phone number column in SharePoint
Validate phone number column in SharePoint

Another Way to format phone number field in SharePoint.

Hey guys, don’t go away. If you don’t like the above approach, we always have another way to Format or validate the SharePoint column.

For this we need to follow below steps:

  1. Create a telephone number column
  2. Select column type as Single line Text
  3. Go to Column Validation
  4. Just copy and paste below formula in Column validation and click on OK

=AND(LEN([Phone])=14,IF(ISERROR(FIND("(", [Phone],1)),FALSE,(FIND("(", [Phone]) = 1)),IF(ISERROR(FIND(")", [Phone],5)),FALSE,(FIND(")", [Phone], 5) = 5)),IF(ISERROR(FIND(" ", [Phone],6)),FALSE,(FIND(" ", [Phone], 6) = 6)),IF(ISERROR(FIND("-", [Phone],10)),FALSE,(FIND("-", [Phone], 10) = 10)),IF(ISERROR(1*CONCATENATE(MID([Phone], 2, 3), MID([Phone], 7, 3), MID([Phone], 11, 4))),FALSE,AND(1*CONCATENATE(MID([Phone], 2, 3), MID([Phone], 7, 3), MID([Phone], 11, 4)) > 1000000000,1*MID([Phone], 2, 3) <> 911,1*MID([Phone], 7, 3) <> 911,1*MID([Phone], 7, 3) <> 555)))

Chris kent given a very good information about this formula.

Hope you like this post.

Comment below if this post helped you.

8 thoughts on “Validate phone number column in SharePoint”
  1. I am the co-founder of JustCBD brand (justcbdstore.com) and I am currently looking to expand my wholesale side of business. It would be great if anybody at targetdomain is able to provide some guidance ! I thought that the most ideal way to accomplish this would be to connect to vape companies and cbd retail stores. I was really hoping if anybody at all could recommend a trusted website where I can purchase Vape Shop Business Data I am already reviewing creativebeartech.com, theeliquidboutique.co.uk and wowitloveithaveit.com. On the fence which one would be the best solution and would appreciate any guidance on this. Or would it be much simpler for me to scrape my own leads? Ideas?

Leave a Reply