1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel and phone numbers

Discussion in 'Business Applications' started by khevlan2, Dec 20, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. khevlan2

    khevlan2 Thread Starter

    Joined:
    Nov 3, 2007
    Messages:
    29
    Hi all

    When I enter a phone number into excel it chops the 0 off from the start. I've checked in
    formatting but can't find the option to stop that.

    Could anybody help me.

    Thanks in advance.
     
  2. Yorkshire Guy

    Yorkshire Guy

    Joined:
    Dec 9, 2003
    Messages:
    563
    Hi KHEVLAN2,

    Easiest I do is to preceed the phone number with a ' character so excel treats what you type as character rather than numeric data.

    Alternatively, achieve the same by inserting spaces within the number.

    HTH
    Hew
     
  3. joed202902

    joed202902

    Joined:
    Mar 7, 2005
    Messages:
    80
    Try formatting the cell(s) as text - that should do it. Don't use the "Special" formatting for phone numbers.
     
  4. WhitPhil

    WhitPhil Gone but never forgotten Trusted Advisor

    Joined:
    Oct 4, 2000
    Messages:
    8,684
    Or, you could use Custom formatting and set up a format of 000-000-0000

    Then a number entered as 0691234567 will be appear as 069-123-4567

    Or, (000)000-0000 and it will appear as (069)123-4567
     
  5. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,175
    It seems the OP was using dots in-between his number like this 703.555.1212. If he uses hyphens (-) or a combo or parens and a hyphen he should be ok.
     
  6. WhitPhil

    WhitPhil Gone but never forgotten Trusted Advisor

    Joined:
    Oct 4, 2000
    Messages:
    8,684
    If you set up the custom format as 000.000.0000 then you can enter the data as 069.123.4567
     
  7. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Huh, Jim? I don't see that. If the OP just wants the preceding 0, formatting as 0000000000 could also be what is wanted - we can't know until the OP comes back. :rolleyes:
    Merry Christmas all!!!!! :D
     
  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    I recommend you do NOT format as text, format as number. If the format is not found under the Special formatting item, use a custom format. :)
     
  9. cherry pie

    cherry pie

    Joined:
    Jun 4, 2007
    Messages:
    345
    I think the 000-000-0000 and the 000.000.0000 formats are really not necessary, if -s or .s are inserted the entry will automatically be translated as text and will not remove the additional 0's unless you want to enter only digits then -'s and .'s will be automatically inserted. Of course if it's only one . then it will be a number and remove the 0 from the beginning.

    I'm assuming khevlan is entring the number without any characters in which case the 0000000000 should do the trick..
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    Cherry pie: I'm having a hard time discerning your post information. When you put..
    .. does that mean dashes and periods, or the combination of those characters together? And as far as using dashes and periods, in the format that does not mean that the value will be textual. Quite the contrary. The value will retain its numerical underlying value, yet the appearance will be much different in lieu of the format. Formats do not change values, hence, formats will not make numerics into textual, nor textual into numerics.

    HTH
     
  11. calvin-c

    calvin-c Banned

    Joined:
    May 17, 2006
    Messages:
    1,098
    No, formats don't change values-but where are you using the value, other than to display it in the formatted cell? People rarely use phone numbers in arithmetic calculations, IME.

    But who can tell what future uses will arise? I think firefytr's implied solution (changing the cell type to Text) is the better one.
     
  12. cherry pie

    cherry pie

    Joined:
    Jun 4, 2007
    Messages:
    345
    yeah meant dashes and periods. Keep typing all day long then too lazy to type an additional word :p

    I'm not sure if we got eachother. What I meant, is that when you type something in a cell and do not choose a specific formatting, excel will automatically choose a format for that. So if you type 123 it will format it as number, if you type 1-2-3 it will format it as date and 1.2.3 will be formatted as text. It kind of thinks for you when sometimes you don't want it to :mad:

    So I meant to say, if you format a cell with 000-000-0000 and type 0123456789 it will display it as 012-345-6789 and the format will be numberic (correct me if I'm wrong). But I recall back in time that it's better to keep phone numbers as text since they use much less space then numbers and you usually don't need to use it as a number (to perform sum or whatever other operations) although the size won't really matter in case of small files but we were taught to think of best space optimization. That's why I recommended typing with the (dashes and periods :)) rather than formatting as a number.
     
  13. cherry pie

    cherry pie

    Joined:
    Jun 4, 2007
    Messages:
    345
    Just saw this after posting :)(y) yeah I do agree that it's best to make it text. Only the thing is that when typing a number with the dashes it will be formatted as text (although will show the format type as general but will treat is as text).
     
  14. calvin-c

    calvin-c Banned

    Joined:
    May 17, 2006
    Messages:
    1,098
    In other uses than Excel, storing numeric data as text can lead to problems. I was recently writing a program that would retrieve data from a database & allow the user to search on it. The data included phone numbers & I thought I'd help by formatting the search string. The user could use the numeric keypad to enter 1234567890 and it would search for the phone number 123-456-7890 which is how it was stored in the database (as text). The problem was that the application entering the data into the database wasn't doing that-it was storing exactly what the user entered. Sure, the user was *supposed* to enter 123-456-7890, but mistakes are always made. Turned out that when they were, the users simply couldn't search for the phone numbers using my application. So I took the automatic formatting out & now it matches the database-it searches for exactly what the user enters.

    BTW, usually text requires more storage than numbers. Text is stored @ 1 byte/character, except in MultiByte Character Sets such as Unicode when it needs more. A 10-digit phone number would therefore require 10 bytes of storage. Numbers are stored in binary, i.e. 5 bytes (2^40) can store numeric values from zero to 1099511627776 (just over 12 digits). Not usually enough of a difference to compensate for the problems of dealing with it as numeric data though.
     
  15. khevlan2

    khevlan2 Thread Starter

    Joined:
    Nov 3, 2007
    Messages:
    29
    Thanks guys.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/663551