SharePoint Phone Number Validation [With Examples]

by
SharePoint online list column validation phone number

In this SharePoint online tutorial, we are going to discuss SharePoint Phone Number Validation including the below topics:

  • How to create a column Validation Phone number in the SharePoint list
  • What’s happening in the formula of SharePoint column validation phone number

Also, Read: SharePoint Online List Calendar View Color Coding

SharePoint Online Validation

SharePoint Online validation is to validate the particular information/data, whether the data which are entered by the user is in a specific format or not.

We restrict the user when they enter the wrong data, this is the purpose of the validation settings in the SharePoint list.

SharePoint Phone Number Validation

As per the above example of the RENA clients list, we will create a column for US client phone numbers, using validation in SharePoint online list.

  • We have a SharePoint list named Semiconductor Clients. By using this list, we will do the Phone Number Validation.
  • To work with the SharePoint Phone Number Validation, we will create a new column (named ClientPhoneNumber) with a Single line of text data type.
  • Expand More options -> Expand Column validation as shown below.
SharePoint online column validation phone number
SharePoint online column validation phone number
  • In Column validation, we have:
    • Formula: (Formula for US-based Phone Number) We can specify the formula for ensuring phone numbers are entered in the form ### ###-####.
    • User Message: When the user enters the wrong format phone number, then the user will get a message. Example: Enter a Valid Phone Number.
  • Apply the formula below inside the Formula box,
=AND(
LEN([ClientPhoneNumber])=12,
    IF(ISERROR(FIND("-", [ClientPhoneNumber],4)),
        FALSE,
        (FIND("-", [ClientPhoneNumber], 4) = 4)
    ),
    IF(ISERROR(FIND("-", [ClientPhoneNumber],8)),
        FALSE,
        (FIND("-", [ClientPhoneNumber], 8) = 8)
    ),
    IF(ISERROR(1*CONCATENATE(MID([ClientPhoneNumber], 1, 3), MID([ClientPhoneNumber], 5, 3), MID([ClientPhoneNumber], 9, 4))),
        FALSE,
        AND(
            1*CONCATENATE(MID([ClientPhoneNumber], 1, 3), MID([ClientPhoneNumber], 5, 3), MID([ClientPhoneNumber], 9, 4)) > 1000000000,
            1*MID([ClientPhoneNumber], 1, 3) <> 911,
            1*MID([ClientPhoneNumber], 5, 3) <> 911
        )
    )
)

Where,

  1. AND = It allows us to pass multiple conditions.
  2. LEN = The function to ensure that the total length of the ClientPhoneNumber is exactly 12 characters (10 numbers, and 2 dashes). This prevents someone from adding extra numbers or characters.
  3. ClientPhoneNumber = This is the internal name of the column validation Phone number in the SharePoint online list.
  4. FIND = The FIND function returns an error if the dash is not in the 4th position and 8th position (in this case, empty) or if the text wasn’t found at all. So if it wasn’t found (ISSERROR), we return false. If it was found then there is a check to see where it was found (FIND returns the character number where the search dash was found).
  5. CONCATENATE = To ensure that not only are the numbers but also that they aren’t all zeroes, sections of numbers are after dash put together using concatenate function and are multiplied by 1. Where the 1st position we use 3 numbers, 5th position we use 3 numbers and in the 9th position we use 4 numbers of a client phone number, and we use If the multiplication fails (because the string can’t be converted to a number)
  6. MID = To ensure that the area code and the prefix don’t equal 911 since these would not be valid numbers.
Validate Phone Number Column in SharePoint online
Validate the Phone Number Column in SharePoint online
  • Click on the Save button. And then refresh the SharePoint list (Semiconductor Clients) once.
  • Add a new item to the list, and provide a client phone number based on US format (###-###-####). You can see the phone number is taken correctly as it satisfies the condition.
Validate Phone Number Column in SharePoint
Validate the Phone Number Column in SharePoint
  • In case, if we provide a client phone number wrongly, then we receive a User Message as Enter a Valid Phone Number (As it does not satisfy the condition).
Create a column Validation Phone number in the SharePoint online list
Create a column Validation Phone number in the SharePoint online list
  • Once we provide the valid phone number, then Click on Save. And at the same time, we can see the new item has been added to the SharePoint list without giving an error. Refer to the image below.
SharePoint online column validation phonenumber
SharePoint online column validation phone number

This is how we can create a column validation phone number in the SharePoint online list.

Have a look: SharePoint Online Modern List Features

Conclusion

In this SharePoint Online tutorial, we discussed what is SharePoint validation, What are the different types of validation settings, and also we discussed

  • How to create a column Validation Phone number in the SharePoint list
  • What’s happening in the formula of SharePoint column validation phone number

Moreover, you may like some more SharePoint Online tutorials: