Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    May 2001
    Location
    Berkeley, California, USA
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    format/number/Text (03 SP2 )

    I formatted a column of phone numbers Format/Cell/Number/Text.
    But when I then edited some of the numbers Excel changed them to dates.
    In the description of "Text" it says "The cell is displayed exactly as entered" Obviously this is not so.
    What is going on?

    Here is the task I am working on. I have a large number of phone numbers in a db. 99% start with "64". Since all the users understand that I am trying to save width I am trying to delete all the "64"s with the replace command.

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

    Re: format/number/Text (03 SP2 )

    Sometimes Excel tries to be too clever.
    You can work around it by replacing 64 with an apostrophe aka single quote <code>'</code>

  3. #3
    3 Star Lounger
    Join Date
    May 2001
    Location
    Berkeley, California, USA
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: format/number/Text (03 SP2 )

    thanks

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: format/number/Text (03 SP2 )

    You can also try

    Format/Cells/Special/Phone Number.

    This is available on the 2003 English version of Excel that I have installed.

    Regards,

    Tom Duthie

  5. #5
    3 Star Lounger
    Join Date
    May 2001
    Location
    Berkeley, California, USA
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: format/number/Text (03 SP2 )

    Thank you but I tried that and it had the same problem. I am trying to represent the the phone number as a 5 digit number in this format: "X-XXXX".
    Hans solution worked fine. I am surprissed I didn't think of it myself. It seems very obvious.

  6. #6
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: format/number/Text (03 SP2 )

    Marc:
    <hr>. . .5 digit number in this format: "X-XXXX".<hr>
    If your phone numbers are all like that, you can pre-format the column with a custom format of: 0-0000 (zeros, not capital O's)

    If you enter less than 5 digits in the cell Excel will pad the start of the number with zeros
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

Posting Permissions

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