display leading zeros in telephone number

A

Andy

When I paste a list of telephone numbers, any leading
zeros are not displayed. (eg. 0944.. shows as 944...) How
can I display them corectly?

Thanks
Andy
 
P

Paul

Andy said:
When I paste a list of telephone numbers, any leading
zeros are not displayed. (eg. 0944.. shows as 944...) How
can I display them corectly?

Thanks
Andy
Click to expand...

Format the cells as text before pasting the data.
 
G

Guest

-----Original Message-----


Format the cells as text before pasting the data.
Click to expand...

Hi Paul
Unfortunately it doesn't work since the original data is
in html format and when pasting, Excel defaults back to
number format. If I change the format back to text after
pasting, then the zeros are 'lost'. If I choose "paste
special" and paste as text, then all the data is pasted to
one column.
Andy
 
D

David McRitchie

and to fix your existing numbers to convert them to text
with a macro
you might take a look at FixUSzip5 and adapt that
to your flavor of telephone numbers (if they are consistent)
http://www.mvps.org/dmcritchie/excel/join.htm#fixUSzip5

If you can't adapt that macro yourself you would have to specify
what the actual phone numbers look like and how they are
formatted under Format, cells, number, custom

Instructions to install and use a macro on my getstarted.htm page.
 
D

David McRitchie

If I copy and paste from HTML, I will get text entries for
"numbers" beginning with zero as Text without the zeros
being stripped off. So it would seem that the HTML that your
are reading from was generated from an Excel spreadsheet
and that they were numbers in the original Excel worksheets
-- in other words you have "round tripping" code in your HTML.

What I posted before still applies. I did not realize that 4 digits
was the entire phone number. You can use a custom format
of 0000 and you could then use the code I mentioned before
changing 5 to 4 and 00000 to 0000
 
B

bob

Andy said:
When I paste a list of telephone numbers, any leading
zeros are not displayed. (eg. 0944.. shows as 944...) How
can I display them corectly?

Thanks
Andy
Click to expand...

Another suggestion...

I copied my phone number from my website, which is in HTML, and pasted it
into Excel.
The numbers displayed as a phone number with all zeros in place.
My cell formatting isn't text; it's "general".

Also, if you want a quick way to strip away HTML, or any formatting for that
matter, copy
the material in question, then paste it into notepad. The formatting won't
be included with
the text or phone number.

First, though, I'd make sure your cell format is "general". Newer versions
of Office know how
to play well with HTML.

Feedback would be cool...

Best Regards,
bob
 
Top