Pasting a number to Excel that begins with 0
 

I need to paste numbers to Excel that sometimes begin with 0. How do I do
this without losing the lead 0? I am using Office 2000. You advice is
appreciated.

Tom, one way to do it:

Format the target cell(s) as Text. Then select Edit | Paste Special | Text
from the Excel menu.

HTH

Alan P.

"Tom" wrote in message
...
I need to paste numbers to Excel that sometimes begin with 0. How do I do
this without losing the lead 0? I am using Office 2000. You advice is
appreciated.

Set up the range you are pasting to with a Custom format that includes
leading zeros.
Copy your data. Use PasteSpecialValues to paste.

Note: the underlying contents of the cell will not actually include the
leading zero, but the leading zero will *display* as specified.
If you want to actually store the leading zero then you'd have to store it
as alpha-numeric text. Numbers don't have leading zeros (except zero
itself).

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"Tom" wrote in message
...
I need to paste numbers to Excel that sometimes begin with 0. How do I do
this without losing the lead 0? I am using Office 2000. You advice is
appreciated.

Alan,

The problem is that the data is being copied out of a web page that includes
multiple columns of data, if I set it to text, it does import the 0s however,
it puts all three columns of data in the same column. Any ideas on cleaning
that up?

Tom Schmitt

"Alan Perkins" wrote:

Tom, one way to do it:

Format the target cell(s) as Text. Then select Edit | Paste Special | Text
from the Excel menu.

HTH

Alan P.

"Tom" wrote in message
...
I need to paste numbers to Excel that sometimes begin with 0. How do I do
this without losing the lead 0? I am using Office 2000. You advice is
appreciated.

you could format the cell as custom and have as many zero's as you want. all
our account codes have 7 numbers, sometimes starting as 00xxxxx. format the
cell as"0000000" and you only need to input xxxxx to get 00xxxxx
George Nicholson wrote:
Set up the range you are pasting to with a Custom format that includes
leading zeros.
Copy your data. Use PasteSpecialValues to paste.

Note: the underlying contents of the cell will not actually include the
leading zero, but the leading zero will *display* as specified.
If you want to actually store the leading zero then you'd have to store it
as alpha-numeric text. Numbers don't have leading zeros (except zero
itself).

HTH,
I need to paste numbers to Excel that sometimes begin with 0. How do I do
this without losing the lead 0? I am using Office 2000. You advice is
appreciated.

The custom format idea works for the display, but it does not carry over to
what I need to do with the number. Each of these cells represents part of an
E-mail address and I need all the numbers to make it work. I am using a
formula such as " When I apply the formula I lose the
zero at the front again...any ideas?

"Tom" wrote:

I need to paste numbers to Excel that sometimes begin with 0. How do I do
this without losing the lead 0? I am using Office 2000. You advice is
appreciated.

Each of these cells represents part of an E-mail
address and I need all the numbers to make it work.

Well, in that case you don't need to treat it as a number, just like you
wouldn't treat zip codes, credit card #s or social security number as
numbers (do you doing any addition, subtraction, multiplication with them?)

Try treating your values as text and forcing Excel to see them as text. If
necessary, look at either the Text() worksheet function or, in VBA, the
Format() function.
= Text(2,"0000")
returns the text value of "00002"
Since you need leading zeros stored, avoid use of numeric data types or
formatting cells as numeric (or General for that matter).

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"Tom" wrote in message
...
The custom format idea works for the display, but it does not carry over
to
what I need to do with the number. Each of these cells represents part of
an
E-mail address and I need all the numbers to make it work. I am using a
formula such as " When I apply the formula I lose the
zero at the front again...any ideas?

"Tom" wrote:

I need to paste numbers to Excel that sometimes begin with 0. How do I
do
this without losing the lead 0? I am using Office 2000. You advice is
appreciated.

Even easier:

Copy all the columns into one column in the way that you have before, and
then use Data | Text to columns option, then in the dialog:
Select either Delimited or Fixed width
Click Next
Check the collumn breaks are where you want them. That's particularly
important if you select the "Fixed Width" option.
Click Next
Select the column that has your number with leading zeroes, and select
"Text" under Column data format (at the top right of the dialog page in the
version that i use)

this will work on the entire column of data if you select all the cells..

HTH

Alan P.


"Tom" wrote in message
...
Alan,

The problem is that the data is being copied out of a web page that
includes
multiple columns of data, if I set it to text, it does import the 0s
however,
it puts all three columns of data in the same column. Any ideas on
cleaning
that up?

Tom Schmitt

"Alan Perkins" wrote:

Tom, one way to do it:

Format the target cell(s) as Text. Then select Edit | Paste Special |
Text
from the Excel menu.

HTH

Alan P.

"Tom" wrote in message
...
I need to paste numbers to Excel that sometimes begin with 0. How do I
do
this without losing the lead 0? I am using Office 2000. You advice is
appreciated.

Thank you, problem solved...

"Tom" wrote:

I need to paste numbers to Excel that sometimes begin with 0. How do I do
this without losing the lead 0? I am using Office 2000. You advice is
appreciated.