Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Excel and phone numbers


(!)

khevlan2's Avatar
khevlan2 khevlan2 is offline
Junior Member with 29 posts.
THREAD STARTER
 
Join Date: Nov 2007
20-Dec-2007, 07:43 PM #1
Excel and phone numbers
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.
Yorkshire Guy's Avatar
Yorkshire Guy Yorkshire Guy is offline
Member with 563 posts.
 
Join Date: Dec 2003
Location: Yorkshire, UK
20-Dec-2007, 07:57 PM #2
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
joed202902's Avatar
joed202902 joed202902 is offline
Member with 80 posts.
 
Join Date: Mar 2005
Experience: Advanced
20-Dec-2007, 11:15 PM #3
Try formatting the cell(s) as text - that should do it. Don't use the "Special" formatting for phone numbers.
WhitPhil's Avatar
Computer Specs
Trusted Advisor - Gone but never forgotten with 8,684 posts.
 
Join Date: Oct 2000
Location: Whitby, Ontario
21-Dec-2007, 10:04 AM #4
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
jimr381's Avatar
Computer Specs
Member with 4,175 posts.
 
Join Date: Jul 2007
Location: Vienna, VA
Experience: Computer Illiterate
21-Dec-2007, 10:14 AM #5
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.
WhitPhil's Avatar
Computer Specs
Trusted Advisor - Gone but never forgotten with 8,684 posts.
 
Join Date: Oct 2000
Location: Whitby, Ontario
21-Dec-2007, 10:19 AM #6
If you set up the custom format as 000.000.0000 then you can enter the data as 069.123.4567
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
21-Dec-2007, 03:36 PM #7
Quote:
Originally Posted by khevlan2 View Post
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.
Quote:
Originally Posted by jimr381 View Post
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.
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.
Merry Christmas all!!!!!
Zack Barresse's Avatar
Computer Specs
Member with 5,434 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
21-Dec-2007, 06:10 PM #8
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.
cherry pie's Avatar
cherry pie cherry pie is offline
Computer Specs
Senior Member with 345 posts.
 
Join Date: Jun 2007
Location: Yemen
Experience: Advanced
21-Dec-2007, 06:11 PM #9
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..
Zack Barresse's Avatar
Computer Specs
Member with 5,434 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
21-Dec-2007, 06:19 PM #10
Cherry pie: I'm having a hard time discerning your post information. When you put..
Quote:
Originally Posted by cherry pie
-'s and .'s
.. 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
calvin-c's Avatar
Account Disabled with 1,098 posts.
 
Join Date: May 2006
Experience: Advanced
21-Dec-2007, 06:25 PM #11
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.

Last edited by calvin-c; 21-Dec-2007 at 06:27 PM.. Reason: additional comment
cherry pie's Avatar
cherry pie cherry pie is offline
Computer Specs
Senior Member with 345 posts.
 
Join Date: Jun 2007
Location: Yemen
Experience: Advanced
21-Dec-2007, 06:32 PM #12
Quote:
Originally Posted by firefytr View Post
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
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

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.
cherry pie's Avatar
cherry pie cherry pie is offline
Computer Specs
Senior Member with 345 posts.
 
Join Date: Jun 2007
Location: Yemen
Experience: Advanced
21-Dec-2007, 06:39 PM #13
Quote:
Originally Posted by calvin-c View Post
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.
Just saw this after posting 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).
calvin-c's Avatar
Account Disabled with 1,098 posts.
 
Join Date: May 2006
Experience: Advanced
21-Dec-2007, 07:02 PM #14
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.
khevlan2's Avatar
khevlan2 khevlan2 is offline
Junior Member with 29 posts.
THREAD STARTER
 
Join Date: Nov 2007
21-Dec-2007, 07:31 PM #15
Thanks guys.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑