Annoying problem inserting numbers into cells

  • Thread starter Thread starter David Nebenzahl
  • Start date Start date
D

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