Actual solution

  • Thread starter Thread starter Pam F-M
  • Start date Start date
P

Pam F-M

I know this thread is old, but I have been experiencing this same problem, and formatting the fields as numbers without decimals was not working, because ISBNs, EANs, and account numbers often start with zero, and excel drops zeros from the front of numbers.

If you have the same problem, I finally just FOUND THE SOLUTION!:

Choose "Custom", at the bottom of the number style list. Most of my numbers are 13-digit ISBNs, so I entered a custom format made of 13 zeros. Now all numbers entered in this column show all 13 digits at all times.

examples:
0000000000000
000-0-00000-000-0

This seems to be the EASIEST SOLUTION, and is built right into excel. Why wasn't this suggested to the OP immediately, I wonder?

*If your numbers are not a standard character length, labeling them as zip codes in the "Special" menu also works. (It doesn't truncate the numbers).
 
If you type in the adjacent cell ="'"&A2 presuming that the scientific notation is in cell A2 it will then show the number correctly. Drag or double click to change the other cells in the column.

Then format the cells as text

Then use copy and replace to get rid of the '

Now you have the cells displayed correctly

Thanks Mike
 
Use Open Office. It doesn't have this "problem" :-)

If you can get your csv file to wrap each number fields in a =TRIM("") like this

=TRIM(12345678901234567890)

It will import into excel as text.
 
Back
Top