Many people have asked how they can retain leading zeros and there are a

variety of solutions offered. However, I haven't seen my situation and I

haven't found a solution. I need to copy and paste into Excel many tables of

accounting data. The tables have multiple columns. The values may contain

valid leading zeros and the values are not fixed width. There are thousands

of records so entering a leading ' is not practical.

I have tried several solutions and none work. Specifically, I have tried

preformating the cells in a new spreadsheet as text before I paste. The

leading zeros are dropped. I have tried paste special/text. All the columns

from the source table end up in column A.

Is there a way to disable auto format? Is there anything else I can try?

Thanks,

David

David Biddulph said:

The reason that you can't see any response to a custom format after you've

used the LEFT function is that custom formats don't work on text strings,

and LEFT returns a text result. If you want to convert the text value to a

number use --LEFT(...)

The reason that your LEFT function doesn't see your leading zeros is that

the custom formatting affects only the display, not the underlying content,

so when the formula refers to F2 it sees the number in the cell, not your

formatted display version. If you want the LEFT function to see the

formatted version, replace the F2 references by TEXT(F2,"000000000000").

--

David Biddulph

I am using Excel 2003. Excel is dropping the leading zeros from 12 digit

UPC

numbers I paste into a spreadsheet. I know how to restore the leading

zeros

using the special cell formatting option "000000000000" to make the number

12

digits, this pads the beginning of the number with leading zero's to fill

it

out to 12 digits. However, I also need to drop the last digit (the check

digit) from the UPC numbers that I am pasting. When I use a formula (in

this

case =LEFT(F2,LEN(F2)-1); I apply this formula to the entire column to the

right of the column with the UPC numbers [column F] and it drops the last

digit) to drop the last digit from the UPC, the numbers that are generated

unfortunately seem to have dropped the lead zeros again. And once I have

formatted this column with the formula to generate the UPC minus the check

digit, I don't seem to be able to apply a custom number format to the

cells

to make them 11 digits, thus restoring the lost leading zeros. Is there

any

way to make Excel quit dropping leading zeros? Or is there any way to

both

have a formula applied to a column AND a custom number format? I am

totally

stuck here.

Thanks for any help