Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Data Validation Format (Excel 2002)

    Hi

    I am trying to data custom validate a cell so that mobile phone numbers can only be entered as 12345-123456 is this possible please.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Validation Format (Excel 2002)

    See the attached workbook. It contains a module with a custom function ValidMobileNumber.

    Cell A1 is where the user enters a phone number. Cell B1 contains a formula used in Data | Validation for A1. You can hide column B if you like.

    Perhaps someone else will come up with a less clunky solution.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Data Validation Format (Excel 2002)

    Hi Hans

    Thanks for your reply , Entering 12345-123456 in cell A1 one is not a problem but I can't use B1 so I used IV1 But I get #Name instead of true or false.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Validation Format (Excel 2002)

    Did you copy the custom function (or the entire module) from the workbook I posted into your workbook? This must be done in the Visual Basic Editor.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Data Validation Format (Excel 2002)

    Hi Hans

    Sorry forgot to enter the function code!! all now OK


    Thanks

    Braddy

    <img src=/S/bash.gif border=0 alt=bash width=35 height=39>
    If you are a fool at forty, you will always be a fool

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Data Validation Format (Excel 2002)

    This function could probably be improved upon, but I tested it under different conditions, and it seems to work well.

    =AND(SEARCH("-",A5)=6,AND(LEN(LEFT(A5,5))=5,CHAR(45)="-",LEN(RIGHT(A5,6))=6),LEN(A5)=12)

    Braddy:
    1. Select the cells that must be validated, and choose Data Validation.
    2. In the Allow box, select formula.
    3. Paste the formula above into the box.
    4. Supply the necessary error message.
    5. Choose OK.
    Regards,
    Rudi

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Data Validation Format (Excel 2002)

    Hi Rudi

    Thanks for the reply, I will look into it later.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Data Validation Format (Excel 2002)

    Hi Rudi,
    A couple of comments:
    1. If len(A5)=12, then left(A5,5) will always return 5 characters, so is an unnecessary test, as is right(A5,6), which will always be 6 characters.
    2. CHAR(45) equals "-"; it either does or it doesn't and won't be affected by whatever you enter! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    3. This does not ensure that you enter numbers, rather than letters or punctuation. For instance, I can enter "1234)-ghjklm".
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Data Validation Format (Excel 2002)

    Thanx Rory,

    I have removed the LEN functions, and added functionality to test for only numbers. Is this better?

    =AND(IF(ISERROR(SEARCH("-",A5)),FALSE,TRUE),AND(ISNUMBER(VALUE(LEFT(A5,5))) ,IF(MID(A5,6,1)="-",TRUE,FALSE),ISNUMBER(VALUE(RIGHT(A5,6))),LEN(A5) =12))

    cheers
    Regards,
    Rudi

Posting Permissions

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