16 digit number, last number changes to 0

pcar

New Member
Joined
Jun 26, 2008
Messages
8
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?
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Hi and welcome to the board!!

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

lenze
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Try changing the number format to Custom and in the type box put:
0000000000000000
 

pcar

New Member
Joined
Jun 26, 2008
Messages
8

ADVERTISEMENT

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
Joined
Jun 26, 2008
Messages
8
Tried that, too, and it still changes the last number to a zero.
 

pcar

New Member
Joined
Jun 26, 2008
Messages
8

ADVERTISEMENT

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

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Jun 26, 2008
Messages
8
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.....
 

Watch MrExcel Video

Forum statistics

Threads
1,123,494
Messages
5,602,004
Members
414,490
Latest member
Rip181

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top