Weird number formatting

  • Thread starter Thread starter Dave McCullough
  • Start date Start date
D

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.
 
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
 
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.
 
Back
Top