Search code examples
validationgoogle-sheetsgoogle-sheets-formula

How to use data validation to force google sheets column into phone number formatting

I have a column where phone numbers should be entered. I want to use data validation in GOOGLE SHEETS to not allow any input that isn't in standard phone number format {{ example: (302) 242-0772 }}

I have this rule currently in my sheet, but it's basically giving an issue for anything input (even when something is in phone number format).

I also have it set to the correct range (all of column F, starting with F3)

=AND(ISTEXT(F3), REGEXMATCH(F3, "^(\d{3}) \d{3}-\d{4}$"))

I realize that the rule works when I actually type in the parantheses/hyphen. However, because they are being set with google sheets formatting and only a 10 digit number is shown in the formula bar, the data validation doesn't work.

I originally tried to just limit the column to 10 digit numbers, but that wasn't working either (denying every entry). This was my rule for that logic:

=AND(ISNUMBER(F3), LEN(F3) = 10)

Any idea how I can solve this issue? Ultimately, I have a 10 digit number that is being formatted as a phone number - I want to lock it so no user can input anything other than 10 digits.

Solution

  • You have stated your requirement for 10 digit numbers.

    In Google Sheets, you can limit the user to input 10 digits, and also be able to format as a number by doing the following:

    Set Data validation criteria for the range as "Is between".

    Input the lowest number 1000000000 and the highest number 9999999999

    Then set Advanced options to "Reject the input"

    Then click "Done"

    enter image description here

    Once done, go to Format > Number > Custom number format

    Use this format:

    (000)000-0000

    Then click "Apply"

    Now the 10 digit number will be accepted, but other lengths will be rejected, and the number will be formatted as a phone number.

    Note: this will reject numbers where leading digits in the 10 digit string are zeros.