Weird number formatting

[Dave McCullough]

When I enter (or import) 10-digit phone numbers into
Excel XP, they are usually converted into a shorter
format. For example, 6042151797 became 32768 in cell A1.
A different phone number entered in cell A2 also became
32768. A third 10-digit number entered in cell A3 became
120. All cells are formatted with general number
formatting. Occasionally, the 10-digit number remains a
10-digit number. Very rarely, I am able to apply phone
formatting and get (###) ###-####. Column width does not
seem to affect the result. Can anyone remedy (or explain)
this behaviour? Thanks in advance.

 

[Coleen]

Not sure about Excel XP, but in 2000, the results that you are getting sound
like it is reading it as a date - try this trick it works in Excel 2000 -
select the entire column, go to the Data Menu, select Text to columns,
follow the steps in the wizard using the defaults, and give it a completely
different destination column. When it has converted the column, try
re-formatting the new column with the correct formatting you want -
numeric - and see if that works for you. I get that a lot if I am importing
the data from other applications. Hope it works in XP...

Coleen

 

[Norman Harker]

Hi Dave!

Have you tried to pre-format:

Format > Cells > Number > Special
Select Telephone

Then entries of:

9895677744
give formatted return of (989) 567-7744

Or you could just pre-format as text and enter the numbers using the
parentheses and spaces that you want.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Thursday 17th July: Iraq (Ba'ath Revolution
day holiday reported by 14-Jul-2003 NYT as cancelled), Israel (Shiva
Asar B'Tammuz), Puerto Rico (Munoz Rivera Day), South Korea
(Constitution Day), US Virgin Islands (Hurricane Supplication Day),
Observances: Fast of 17th Tamuz (Judaism)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.