Leading Zeros; How to Keep ?

[Bob]

Hello,

Using Excel 2007

This really has me going around in circles.

Have a column of numbers I copied from elsewhere.
Copied fine, no problems.

The numbers are e.g.:

0000
0030
etc.

They all have zeros in front of them, or are truly 0000.

I want to handle them as Text, and perhaps change a digit here and there.

Excel insists on dropping the leading zeros when I select a cell, even
when I format the column (numbers already in them) as Text.

So, in the column may be 0000, but when I pull it up to modify it, it
shows as 00, and that is what it will put back in the cell.
0030 would become just 30.

Then, I thought, I'd be really clever.
I first formatted a blank column as text, copied the numbers into it,
then re-copied back.
Didn't work. Still wants to drop leading zeros.

How do I get around this, please ?

Thanks,
Bob

 

[joeu2004]

Hello,
The numbers are e.g.:
0000
0030
etc.
They all have zeros in front of them, or are truly 0000.

I presume you mean you have numbers formatted as Custom 0000. Right?

I want to handle them as Text, and perhaps change a digit
here and there.

If the original source had 4 digits with leading zeros, it would have
been prudent to paste them into cells that had been formatted as Text
beforehand.

But having not done that, you might consider doing the following....

In a parallel column, enter the formula =TEXT(A1,"0000") and set the
format to Text. That creates the text that you want.

If you wish, you can used copy-and-paste-special-value and set the
format to Text in order to replace either the original column or the
parallel column with the text constants.

(If do not set the format to Text, the data might revert to numeric
type if you edit the cells later.)

 

[Bob]

Hi,

Just a quick thanks; appreciate it.

Bob