Custom formatting in Excel 2007

[Jenny Barker]

I am trying to set up a format for one column in my worksheet. The column
will be used to hold a formatted account number. When I use the custom
format 0-000-000-00-000000-00-0 or same setup substituting ?, Excel formats
the data correctly but substitutes a 0 for the final digit. This happens
regardless of what the last digit is. I have tried changing the data entry
to entry+1, but that doesn't work either. Even if I edit the data directly
to change the last digit from 0 to any other digit, Excel goes right back to
substituting 0. What am I doing wrong?

 

[ker_01]

Jenny-

You aren't doing anything wrong (other than expecting more from Excel than
it is designed to provide).

Try this experiment; in an unformatted cell, enter the number:
123456789012345
It should show as expected.

Now enter a number that is one digit longer, in another cell:
1234567890123456

you will see that excel does not store the last digit, but instead shows the
value as
1234567890123450

You are running into a limitation on how many digits Excel can store; search
the help file for "Worksheet and workbook specifications and limits" and you
will see: "Number precision 15 digits". The problem is that your account
numbers are simply longer numbers than Excel can handle.

One option would be to store the number as a string (text) instead of a
number, and use text conversion functions to pull the number back out if/when
you need it... but whether or not that would work depends a lot on what you
are doing with the data and if you need to store the account number in
numeric form to pass to other systems.

Another option would be to break the account number across two columns; that
would give you the necessary precision, but again, the usefulness of that
solution depends a lot on how you need to use the data.

A third option would be to store the number as a string, and use VBA for any
calculations that require all 16+ digits, but if these are account numbers,
then I imagine you are using this field as a key, not actually running
calculations off of it, so this may not add value.

In any case, if you are crunching numbers that then need to be uploaded to
another system (using that account number) you may need to explore the option
of using a CSV file rather than uploading an Excel file directly; that way
you could recreate the account number (in whatever format) in the CSV file
from Excel, probably requiring some VBA.

HTH,
Keith