Pasting a number to Excel that begins with 0

T

Tom

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.
 
A

Alan Perkins

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.
 
G

George Nicholson

Set up the range you are pasting to with a Custom format that includes
leading zeros.
Copy your data. Use PasteSpecial>Values 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,
 
T

Tom

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
 
A

alex s via OfficeKB.com

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
 
T

Tom

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 "a0"&d23&"@xxxxx.com" When I apply the formula I lose the
zero at the front again...any ideas?
 
G

George Nicholson

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

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,
 
A

Alan Perkins

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