Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Telephone cell validation

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hey,

    this there any way i could validate a cell so that the only numbers can be inputted and that the first 5 digits are in a bracket and that after 3 digits it adds a space. Example: 01286674110 with validation = (01286) 674 110

    Thanks

  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The formatting is easy: Choose Format>Cells and select the custom option. In the type field, enter:

    (#####)### ###

    This will automatically insert the brackets and a space when someone enters an 11 digit number

    [ This Message was edited by: lenze on 2002-05-08 14:10 ]

    [ This Message was edited by: lenze on 2002-05-08 14:11 ]

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-08 13:53, dirty_cofi wrote:
    hey,

    this there any way i could validate a cell so that the only numbers can be inputted and that the first 5 digits are in a bracket and that after 3 digits it adds a space. Example: 01286674110 with validation = (01286) 674 110

    Thanks
    I will do it as a two step process ...
    1. DATA|VALIDATION|SETTINGS|Allow|Whole_Number
    between 1111111 and 99999999999
    2. CUSTOM format the number as "("00000")" 000 ###

    Hope This Helps!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  4. #4
    New Member
    Join Date
    May 2002
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey,

    THANKS your a life saver!!!!

    Later

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    BTW, "validation" is quite different from "formatting".

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-08 14:44, Mark W. wrote:
    BTW, "validation" is quite different from "formatting".
    Hi Mark:
    Are you referring to the technicality -- in that the OP had asked for a Validation Rule, and what I proposed was a combination of Validation and Custom formatting ...

    or

    You are referring to some problem that my proposed solution might cause.

    Your valuable comments will be greatly appreciated.

    Regards!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  7. #7
    New Member
    Join Date
    May 2002
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hey,

    sorry don't understand the question but basically your answer was perfect and did the job exactly like i wanted!

    Any ideas on how to format a credit card number so it has a space after 4 digits then space after 5 then after 4 then after 5 digits like :

    1234 12345 1234 12345

    When i do it excel kills the last 4 numbers and changes then to zeros!

    thanks

  8. #8
    Board Regular RichardS's Avatar
    Join Date
    Feb 2002
    Location
    Victoria, Australia
    Posts
    761
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Excel has a limit of 15 with numbers. Not sure how you are going to get around it, but I think a similar question has been asked before on this site. Try searching on credit card.
    Richard

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-09 23:36, dirty_cofi wrote:
    hey,

    sorry don't understand the question but basically your answer was perfect and did the job exactly like i wanted!

    Any ideas on how to format a credit card number so it has a space after 4 digits then space after 5 then after 4 then after 5 digits like :

    1234 12345 1234 12345

    When i do it excel kills the last 4 numbers and changes then to zeros!

    thanks
    As RichardS said, you can do it using a number format. However you can simulate the collection of numbers as a text string and then use DATA|VALIDATION. This will not be fool-proof, but he following formula in DATA|VALIDATION will give you the capability that you asked for:

    =AND(LEN(A5)=20,MID(A5,5,1)=" ",MID(A5,11,1)=" ",MID(A5,16,1)=" ")

    the above formula is for DATA|VALIDATION for cell A5, where it ensures that the 5th character, 11th character, and 16th characters are 'space'; and the length of the string is 20. It does not check whether each character being entered is a numeric character or not.

    Hope This Helps!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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