16 digit number, last number changes to 0

[pcar]

I work for a financial institution, and we are creating a worksheet that lists card numbers. The card numbers, like most other card numbers, are a 16-digit mod-10 number. We're not doing any of the calculations in excel, we're just simply entering in the card number as it appears on the card. The problem is that the last digit is changing to a "0". What is going on and how can we stop it?

 

[lenze]

Hi and welcome to the board!!

That is a limitation in Excel. Try formatting your cells as Text

lenze

 

[Lewiy]

Try changing the number format to Custom and in the type box put:
0000000000000000

 

[schielrn]

Welcome to the Board!

See this:

http://support.microsoft.com/kb/269370/en-us

 

[pcar]

Tried that .... when the cell is formatted as text, the following appears:
<TABLE style="WIDTH: 106pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=141 border=0 x:str><COLGROUP><COL style="WIDTH: 106pt; mso-width-source: userset; mso-width-alt: 5156" width=141><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 106pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=141 height=17 x:num="5850290003004040">5.85029E+15</TD></TR></TBODY></TABLE>

 

[pcar]

Tried that, too, and it still changes the last number to a zero.

 

[pcar]

Thanks! I'm actually "returning after a long absence".

 

[Johnny C]

I work for a card company too and we have had the same problem. Are you entering it directly into an XLS or via CSV? Via CSV will always get truncated by excel and replaced with a zero, TXT files the same, if you get the data from SAS it needs exporting as an Excel workbook.

Setting the format to Text works for me.

You can also fool Excel into thinking a value is text, so even if it displays in +E15 format, just set another cell to =""&(original cell), I just tested it and it worked fine. The TEXT function copies the E+15 format for some reason so can't be used.

 

[Richard Schollar]

Hi

If you have already entered the numbers into a General or numeric formatted cell then I'm afraid you have lost the 16th digit. You will need to format the cells to text and reenter the card numbers in the cells.

 

[pcar]

Thanks to everyone for your responses. We're getting around it, now, with your help.
Does anyone know why this happens, or is it like the mystical force that removes socks from the dryer and will never be explained.....