i enter a 16 digit number the last digit converts to a 0...help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I receive incoming spreadsheets with several columns of data. One column
contains 16 digit numbers. When I open the file the number converts the last
digit to "0". I don't know how to pre-format the column to text before I
open the file and then it is too late. I need help!
 
Excel's limit for numbers is 15 digits.
What format are your "incoming spreadsheets" in?
 
There are usually 500 to 800 rows of data, so the file comes over zipped. It
is in standard EXCEL format. This involves credit card numbers, some of my
older clients understand to insert "-" every 4 digits, so they aren't a
worry. But many of my clients are less knowlegable or just stubborn and
place it as a continuous 16 digit number. So the number may be incorrect
when my client enters it and not when I open the zip file. Is there any way
to correct the number after the 16th digit has converted to a "0"?
 
No, you either need to preformat your template as text, or have all entries
prefixed with an apostrophe. Once the last digit becomes 0 the data is
gone.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------­------------------------------­----------------
 
giterdun said:
I receive incoming spreadsheets with several columns of data. One column
contains 16 digit numbers. When I open the file the number converts the last
digit to "0". I don't know how to pre-format the column to text before I
open the file and then it is too late.

It is in standard EXCEL format. This involves credit card numbers, ....
as a continuous 16 digit number. So the number may be incorrect
when my client enters it and not when I open the zip file. Is there any way
to correct the number after the 16th digit has converted to a "0"?
Click to expand...

If they are saving it in Excel (XLS) format, and the numbers are
already converted when you open the file, then the numbers are already
converted when they save the file (converted as soon as they press
enter or leave the cell), and the last digit is not recoverable from
the XLS file because it longer exists there. If they were saving the
data in some way that preserves all 16 digits (by preformatting the
column as text, or by entering a leading apostrophe in each cell), then
you would see all 16 digits when you opened the file.

Even if they were saving text files with an XLS extension to make them
look like Excel files, in which case all 16 digits could be in the file
before you opened it, you would automatically get Excel's Text Import
Wizard when you opened the file, which would allow you to format the
column as text in the 3rd page of the Wizard.

The only scenario I can think of in which the data is there in the XLS
file but automatically disappears when you open the file is if they are
actually saving in "comma separated values" format (CSV), then changing
the extension to XLS to make it look like an Excel file. CSV preserves
all 16 digits, but Excel opens CSV files without giving you the Text
Import Wizard, automatically converting the numbers.

To test whether that or something similar is the case, try changing the
file's extension from .xls to .txt, and then opening the file in Excel.
Then, in the 3rd page of the Text Import Wizard, change that column's
format to Text.

If that doesn't help, I think you'll need to persuade them to change
their habits.


Good Luck,

Greg Lovern
http://PrecisionCalc.com
Eliminate Hidden Spreadsheet Errors
 
Back
Top