Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: custom format for phone numbers

  1. #1
    Guest

    Default

    I want to use a custom format for phone numbers, the one excel has uses () I want to use
    Ex. Numbers entered as 2222222 1232222222 18002222222 should be formatted as 222-2222 123-222-2222 1-800-222-2222. Can this be done by a custom format? I have tried to modified the format for phone numbers but cant get it to work. Thanks

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Florida
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Go to format cells, custom. Type in ###-#### for the first number. Go Back to custom and type in ###-###-####. Continue this till all your custom numbers are installed.

    [ This Message was edited by: kinkyparamour on 2002-03-13 06:55 ]

  3. #3
    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

    Try something like this:

    [>9999999](000)000-0000;000-0000 You may need to modify it to include the 1 prefix, such as [>9999999]1-(000)000-0000;000-0000 wher the user would not enter the leading 1.

  4. #4
    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

    A "number" entered as "2222222 1232222222 18002222222" will not be treated as a numeric value by Excel because of the imbedded space characters and; therefore, its display will not be governed by an custom number format. Furthermore, if you exclude the spaces the value will exceeds Excel limit of 15-digit integers.

  5. #5
    Guest

    Default

    I need to be able to format the whole column as one format and it will change the numbers as need based on what was entered

  6. #6
    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

    On 2002-03-13 07:06, Anonymous wrote:
    I need to be able to format the whole column as one format and it will change the numbers as need based on what was entered
    See my posting above. You cannot enter these as numeric values. Custom number format can only be applied to numeric values.

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Florida
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark,
    You are right. I stand corrected........Now if I could just figure out the right answer I will feel a lot better.....;o)

  8. #8
    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

    I think I'm confused as to what you have. As I understand it, you have imported a column with phone numbers of various lengths, such as 222222 and 5554446666 and 1555444666. Is that correct or do you have 222222 5554446666 1555444666 in one cell? If you do, Mark is correct, that is not a number format. If it's the first scenario, then use the custom format idea I previously posted. Replace the () with - if you wish.

  9. #9
    Guest

    Default

    Sorry for the confusion, they are three different numbers, showing if somebody enters a local number (2222222), area code and number(1232222222), or 1 area code and number(11232222222)

  10. #10
    Guest

    Default

    Lenze yours put -- in front of the local numbers and did not put - between the 1 and area code

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
  •