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.
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.
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
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.
- Go to List where you want to validate the Telephone Number column in SharePoint.
- Click on Gear icon from Top right corner and Select List Settings
- Then Click on Add new Column and give a name like “Telephone Num”.
- Type of Column must be Calculated (calculation based on other columns)
- Add a formula =TEXT([Telephone No.],”(000) 000-0000″)
- click on OK.
This will display a general number column in the United States telephone number format.
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:
- Create a telephone number column
- Select column type as Single line Text
- Go to Column Validation
- 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.