Annoying problem inserting numbers into cells

[David Nebenzahl]

I've run into a very annoying problem that I can't seem to find a
satisfactory solution to. What I'm doing is trying to track a bunch of
eBay auctions. One of the columns is for the auction number, which is a
12-digit number. I want the number to appear as that 12-digit number,
and to be treated as text (not doing any calculations with it), *not* as
a number.

Anytime I copy and paste these numbers (from eBay web pages into Excel),
Excel insists on reformatting the number as a number, even when I've
formatted the cell as text. For instance, auction number 320173256337
appears as "3.2E+11". Not useful at all for my purposes.

I said I formatted the cells first as text, but it seems to me that the
act of pasting one of these numbers into a cell forces the cell to be
formatted as a number ("general"). But if I then change the format to
"text", the number still appears in exponential format.

The one work-around I've found is to use the "format as" feature of the
Excel I use at work. (Here at home, I've got an earlier version that
doesn't have this feature.) If I tell Excel to retain the current
formatting, the number then appears as a number (that is, a string of
digits), like I want.

Help! I've got a zillion of these to enter, and this is really slowing
me down. Any help or work-arounds would be appreciated.

 

[Guest]

I had same annoying problem previously. One workaround I use is Number
Format-Custom and put 12-zeroes in the Type field.

Hope this helps.

 

[T. Valko]

One way.

Preformat the cells as TEXT.

Then when you copy and go to paste you should have a right mouse button menu
option to paste special as either html, unicode text or text. Either one of
the text options should work for you. (works for me, Excel 2002)

 

[T. Valko]

That doesn't work for me (Excel 2002).

Custom format: 000000000000

Paste 320173256337 into the cell and it still reverts to 3.2E+11 (with a
column width of 8.43 Arial font size 10). Widening the column doesn't help,
either.

You can also paste, still getting 3.2E+11, then format as NUMBER (no decimal
places).