# 16 digit number, last number changes to 0

#### pcar

##### New Member
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?

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### lenze

##### Legend
Hi and welcome to the board!!

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

lenze

#### Lewiy

##### Well-known Member
Try changing the number format to Custom and in the type box put:
0000000000000000

#### pcar

##### New Member

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

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

#### pcar

##### New Member

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

#### Johnny C

##### Well-known Member
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

##### MrExcel MVP
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

##### New Member
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.....

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,282
Messages
5,836,410
Members
430,425
Latest member
xlsee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back