My suggestion was to open the *.txt file directly.

I did not suggest DataImport External Data.


Gord

On Wed, 22 Apr 2009 08:52:02 -0700, 4110
wrote:

Thank you for your reply. Per your suggestion, I pasted a table into NotePad
and saved it as a .txt file. Then I used Data/Import External Data/Import
Data to import into Excel. It imported, but with problems. I couldn't find
a delimiter that worked. If I used spaces I ended up with far more than the
three actual columns. With any other delimiter I got everything in one
column. Note: If I paste straight from the source it properly becomes three
columns in Excel (but without the leading zeros). Something about going
through Notepad removes what Excel sees to create three columns from the
original source.

David

"Gord Dibben" wrote:

Copy and paste into a text editor like NotePad.

Save as a *.txt file.

Open that file in Excel and the Text Import Wizard will allow you to
designate the data as Text.


Gord Dibben MS Excel MVP

On Wed, 22 Apr 2009 08:18:09 -0700, 4110
wrote:

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" wrote:

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

"Carl Nash" <Carl wrote in message
...
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