+ Reply to Thread
Results 1 to 10 of 10

Data Validation: Phone Number

  1. #1
    Registered User
    Join Date
    02-20-2013
    Location
    Oregon
    MS-Off Ver
    Excel 2010
    Posts
    18

    Data Validation: Phone Number

    Hello Master Excel Gurus

    I imagine this question has been asked before, but when I looked through other posts I didn't see a formula that would work for me.

    I need a data validation formula for cell O8 that would only allow data entry users to enter a phone number in the format ###-###-####. For example, a phone number like 313-867-5309.

    I am hoping a simple formula like =OR(ISNUMBER(o8), o8="-")) might work, but I am also open to using named ranges. I have tried the above formula and many variations of it, but with no success.

    Any help would be greatly appreciated!!

    Much Thanks!

  2. #2
    Forum Contributor
    Join Date
    05-24-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    291

    Re: Data Validation: Phone Number

    Hi Polarx

    Select O8 go to format cell - custom - below type put ###-###-####

    then enter 10 digits is in O8

  3. #3
    Registered User
    Join Date
    02-20-2013
    Location
    Oregon
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Data Validation: Phone Number

    Hi Amy, Thank you for the reply!

    Very interesting idea...I will try it out!

  4. #4
    Forum Contributor
    Join Date
    05-24-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    291

    Re: Data Validation: Phone Number

    Hi PolaxX,

    do not forget to mark thread as solved

  5. #5
    Registered User
    Join Date
    02-20-2013
    Location
    Oregon
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Data Validation: Phone Number

    Thanks Amy, but that idea didn't work for me.

    I was still able to enter a phone number as text without an error message being produced. I think it will need a data validation formula.

    Thanks for the reply though!!

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Data Validation: Phone Number

    Try...

    Custom Format
    • Right click cell (or Ctrl + 1)
    • Format Cells
    • Number
    • Custom
    • Type: Phone Number

    Then

    Data Validation
    • Data >> Data Tools >> Data Validation
    • Setting >> Custom
    • Formula: =AND(ISNUMBER(O8),LEN(O8)=10)

    Apply an input message or Error Alert if you wish...
    HTH
    Regards, Jeff

  7. #7
    Registered User
    Join Date
    02-20-2013
    Location
    Oregon
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Data Validation: Phone Number

    Good Morning Jeff, Big Thanks for the reply!!

    I gave it a try, but when I tested the formula it produced an error when I entered the phone number. I will keep working with your formula. What does the '10' mean in your formula?

    Much Thanks!!

  8. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Data Validation: Phone Number

    The 10 represents the 10 digits of the phone number.

    You could also try...

    Data Validation
    • Data >> Data Tools >> Data Validation
    • Allow: Whole Number
    • Data: between
    • Minimum: 1000000000
    • Maximum: 9999999999
    Last edited by jeffreybrown; 07-29-2013 at 12:45 PM.

  9. #9
    Registered User
    Join Date
    02-20-2013
    Location
    Oregon
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Data Validation: Phone Number

    Your formula does work! The issue I was having was that I was trying to enter the dashes "-" with the phone number; which produced the error.

    When I just entered the digits of the phone number it worked just fine--Amazing!!

    Thank you for the valuable help!

  10. #10
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Data Validation: Phone Number

    Ok great...

    You're welcome…glad you have a workable solution and thanks for the feedback...

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Is it possible to make Excel dial a phone number from my smart phone?
    By D_Step in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-19-2013, 06:11 PM
  2. Data normalizing for phone number extraction
    By 2by4 in forum Excel General
    Replies: 1
    Last Post: 05-30-2013, 12:42 AM
  3. [SOLVED] Merge 2 sheets by common data? Ex: sheet 1 has address, no phone. Sheet 2 same and phone
    By KathleenTurnis in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 12-28-2012, 04:18 PM
  4. Phone bill data analysis - sorting cost by phone nos
    By mesoul in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-02-2011, 05:09 AM
  5. validation for phone number
    By Candice in forum Excel General
    Replies: 3
    Last Post: 04-28-2006, 03:55 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1