Easiest Way to Validate Phone Number Column in SharePoint

A few days before, I worked on creating an Event Registration form using the Power platform. For this form, we used a SharePoint list as a data source. To get the proper information from the users, I validated each field in that list, including the Phone number field.

In this article, I will explain how to validate phone number column in SharePoint list.

Validate Phone Number Column in SharePoint

I will explain phone number validations for various countries in this section with examples. Let’s get started! Here is the SharePoint list named Event Registration, which stores registered users’ data.

Here are the columns and the data types of the event registration SharePoint list.

Example 1: Validation For US Format Phone Number [XXX-XXX-XXXX] in SharePoint List

1. Open the SharePoint list, click on the phone number field, and select Column settings ->Edit.

2. Click on More options -> Provide the validation code below in the Formula bar -> Add the text below to the User message. Click on Save.

=AND(LEN(PhoneNumber)=12,
ISNUMBER(VALUE(LEFT(PhoneNumber,3))),
MID(PhoneNumber,4,1)="-", ISNUMBER(VALUE(MID(PhoneNumber,5,3))),
MID(PhoneNumber,8,1)="-",ISNUMBER(VALUE(RIGHT(PhoneNumber,4))))

Please enter valid phone number

Here: XXX-XXX-XXXX

• PhoneNumber =SharePoint list field name.
• LEN(PhoneNumber)=12 ensures the phone number’s length must equal 12 digits.
• ISNUMBER(VALUE(LEFT(PhoneNumber,3))) the left() function fetches the first 3 characters from the phone number, and the Value function converts it into a number.ISNumber() checks whether the result of the value function is a number or not.
• MID(PhoneNumber,4,1)=”-“ This mid function checks whether the 4th character in the phone number is “-” or not.
• ISNUMBER(VALUE(MID(PhoneNumber,5,3))) = Checking whether starting from the 5th character to the next 3 more characters in the phone number are numbers or not.
• MID(PhoneNumber,8,1)=”-“ Ensures the 8th character is “-” or not.
• ISNUMBER(VALUE(RIGHT(PhoneNumber,4)))) = Check whether the right of 4 characters are numbers or not.

3. Now, save the changes, try to add an item to this list, and check the phone number validation. It will show you the custom error message if your phone number is invalid.

Example 2: Validation For Indian Format Phone Number in SharePoint List

So, Indian format number prefixes should be either 7, 8, or 9, but the length of the phone number must be 10 digits.

1. Add the formula below in the Formula bar of the SharePoint list phone number field.

=AND(ISNUMBER(VALUE(PhoneNumber)),LEN(PhoneNumber)=10,OR(LEFT(PhoneNumber,1)="7",LEFT(PhoneNumber,1)="8",LEFT(PhoneNumber,1)="9"))

Here:

• ISNUMBER(VALUE(PhoneNumber)) = It checks whether all phone number values are numbers.
• LEN(PhoneNumber)=10 Ensures phone number length is 10 digits.
• OR(LEFT(PhoneNumber,1)=”7″,LEFT(PhoneNumber,1)=”8″,LEFT(PhoneNumber,1)=”9″) It accepts only if the phone number starts with 7,8 or 9.
• AND() = It returns true only if all conditions within are satisfied.

2. Save the changes and try to add one more item so that when the phone number is not in the format we provided, it displays an error message like in the example above.

Example 3: Validate Phone Number Of Canada (XXX)-XXX-XXXX in SharePoint List

Here is the formula that validates the phone number that matches with (XXX)-XXX-XXXX. Provide this on the Formula bar of the phone number filed in the SharePoint list.

=AND(
   LEN(PhoneNumber) = 14, LEFT(PhoneNumber, 1) = "(",
   ISNUMBER(VALUE(MID(PhoneNumber, 2, 3))),  
   MID(PhoneNumber, 5, 1) = ")",
   MID(PhoneNumber, 6, 1) = "-",
   ISNUMBER(VALUE(MID(PhoneNumber, 7, 3))),
   MID(PhoneNumber, 10, 1) = "-",
   ISNUMBER(VALUE(RIGHT(PhoneNumber, 4))))

Here:

• LEN(PhoneNumber) = 14 Ensure the length is exactly 14 (XXX)-XXX-XXXX
• LEFT(PhoneNumber, 1) = “(“ Ensure 1st character is “(“.
• ISNUMBER(VALUE(MID(PhoneNumber, 2, 3))) Ensure the from position 2nd to next 3 characters should be number.
• MID(PhoneNumber, 5, 1) = “)” Ensuring closing parenthesis is at position 5.
• MID(PhoneNumber, 6, 1) = “-“ Ensuring the hyphen is at position 6.
• ISNUMBER(VALUE(MID(PhoneNumber, 7, 3))) Ensure the characters from the 7th position to next 3 characters are numbers.
• MID(PhoneNumber, 10, 1) = “-“ Ensure the hyphen is at position 10.
• ISNUMBER(VALUE(RIGHT(PhoneNumber, 4))) Ensure the last part is a number.

Save the changes and test it once by providing the phone number. It displays an error message if it does not follow the format.

Example 4: Validate SharePoint List Phone Number Field With German Country’s Phone Number Format [(XX) XXXX-XXXX]

The formula validates the phone number field in the Sharepoint list with the German country phone number format.

=AND(
    LEN(PhoneNumber) = 14, 
    LEFT(PhoneNumber, 1) = "(", 
    ISNUMBER(VALUE(MID(PhoneNumber, 2, 2))),  
    MID(PhoneNumber, 4, 1) = ")",  
    MID(PhoneNumber, 5, 1) = " ",  
    ISNUMBER(VALUE(MID(PhoneNumber, 6, 4))), 
    MID(PhoneNumber, 10, 1) = "-",   
    ISNUMBER(VALUE(MID(PhoneNumber, 11, 4)))  
)

• LEN(PhoneNumber) = 14. Ensure the length is exactly 14 for, e.g., (30) 2342-5656.
• LEFT(PhoneNumber, 1) = “(“ Ensure it starts with a “(. “
• ISNUMBER(VALUE(MID(PhoneNumber, 2, 2))) Ensure area code (2 digits, e.g., 30).
• MID(PhoneNumber, 4, 1) = “)” Ensure “)” is closed.
• MID(PhoneNumber, 5, 1) = ” “ Ensure a space after the closing parenthesis.
• ISNUMBER(VALUE(MID(PhoneNumber, 6, 4))) Ensure the characters from position 6 to the next 4 are numbers.
• MID(PhoneNumber, 10, 1) = “-” Ensure the hyphen after four digits.
• ISNUMBER(VALUE(MID(PhoneNumber, 11, 4))) Ensure the characters from position 11 to the following 4 are numbers.

This way, we can validate the phone number field in the SharePoint list for the required country phone number format.

I hope you understand how to validate a SharePoint list phone number field. I have explained four examples of validating phone numbers in this article. You can follow this article if you are a beginner at SharePoint and apply validation to SharePoint list phone number fields.

Also, you may like:

• Create list view in SharePoint Online
• Add Days to Date in SharePoint Calculated Column
• SharePoint Calculated Column With A Hyperlink
• SharePoint Calculated Column Split String
• SharePoint Calculated Column Concatenate Function
• SharePoint calculated column random number

Preeti Sahu

Preeti Sahu is an expert in Power Apps and has over six years of experience working with SharePoint Online and the Power Platform. She is the co-author of Microsoft Power Platform: A Deep Dive book. As a Power Platform developer, she has worked on developing various tools using Power Apps and Power Automate. She also makes Microsoft 365 videos and shares them on YouTube.