This content is likely not relevant anymore. Try searching or browse recent questions.
SHEETS phone number data validation 1 Recommended Answer 3 Replies 64 Upvotes
How do I perform data validation for a phone number field [i.e. (nnn) nnn-nnnn, or perhaps nnn-nnn-nnnn].
Thanks in advance for any insight you can provide.
Details
Recommended Answer Recommended Answers (1)
Recommended Answer
Most Relevant Answer
Hi, John,
Assuming the phone numbers are being entered into column A
Step 1:
Select column A1:1000 and then select Data validation
Step 2:
Select Custom formula and enter the following custom formula into the field
=regexmatch(A1,"^(\(\d{3}\)\s\d{3}-\d{4}|\d{3}-\d{3}-\d{4})$")
and click Save.
This Data validation will accept both options (nnn) nnn-nnnn or nnn-nnn-nnnn only and reject anything else. In this case, both options will be acceptable.
But if you only want to allow one of the choices then you can use any one of the following for your preference.
(nnn) nnn-nnnn
=regexmatch(A1,"^(\(\d{3}\)\s\d{3}-\d{4})$")
or
nnn-nnn-nnnn
=regexmatch(A1,"^\d{3}-\d{3}-\d{4})$")
Hope you find this useful.
Ben Liebrand
recommended this
marked this as an answer
Recommended based on info available
Our automated system analyzes replies to choose the one that's most likely to answer the question. If it seems to be helpful, we may eventually mark it as a Recommended Answer.
Most relevant based on info available
Our automated system analyzes the replies to choose the one that's most likely to answer the question.
Most Relevant Answer Most Relevant Answers (0)
All Replies (3)
Recommended Answer
Most Relevant Answer
Hi, John,
Assuming the phone numbers are being entered into column A
Step 1:
Select column A1:1000 and then select Data validation
Step 2:
Select Custom formula and enter the following custom formula into the field
=regexmatch(A1,"^(\(\d{3}\)\s\d{3}-\d{4}|\d{3}-\d{3}-\d{4})$")
and click Save.
This Data validation will accept both options (nnn) nnn-nnnn or nnn-nnn-nnnn only and reject anything else. In this case, both options will be acceptable.
But if you only want to allow one of the choices then you can use any one of the following for your preference.
(nnn) nnn-nnnn
=regexmatch(A1,"^(\(\d{3}\)\s\d{3}-\d{4})$")
or
nnn-nnn-nnnn
=regexmatch(A1,"^\d{3}-\d{3}-\d{4})$")
Hope you find this useful.
Ben Liebrand
recommended this
marked this as an answer
Recommended based on info available
Our automated system analyzes replies to choose the one that's most likely to answer the question. If it seems to be helpful, we may eventually mark it as a Recommended Answer.
Most relevant based on info available
Our automated system analyzes the replies to choose the one that's most likely to answer the question.
Recommended Answer
Most Relevant Answer
Ben, great post in response to US phone number types. But this does not seem to be a solution for all phone numbers regardless of country code. For organizations who must input German/Swedish phone number, it seems to be far to complicated to collect and neatly store (using built-in formatting) something as common as phone numbers. Is there a simple tool we can employ for this?
recommended this
marked this as an answer
Recommended based on info available
Our automated system analyzes replies to choose the one that's most likely to answer the question. If it seems to be helpful, we may eventually mark it as a Recommended Answer.
Most relevant based on info available
Our automated system analyzes the replies to choose the one that's most likely to answer the question.
Recommended Answer
Most Relevant Answer
Hi, Caleb,
As you can see, my solution was based on the information that was presented. So you are correct, it only works for US phone numbers. It would be impossible for the formula to cater for every possibility of phone numbers around the world.
As this post was created by John, it satisfies his request. Therefore, you need to create your own post and give all the details needed to meet your scenario. You will have a better chance of someone offering a solution.
Thank you,
Ben Liebrand
recommended this
marked this as an answer
Recommended based on info available
Our automated system analyzes replies to choose the one that's most likely to answer the question. If it seems to be helpful, we may eventually mark it as a Recommended Answer.
Most relevant based on info available
Our automated system analyzes the replies to choose the one that's most likely to answer the question.
This question is locked and replying has been disabled.
Link to post
Delete post?
This will remove the reply from the Answers section.
Notifications are off
Your notifications are currently off and you won't receive subscription updates. To turn them on, go to Notifications preferences on your Profile page.
This reply is no longer available.
Badges
Some community members might have badges that indicate their identity or level of participation in a community.
