Excel Is changing my cell contents (XP)

Topic

New Reply

If I type 4579637981576789 into Excel it gets displayed as 4.58E+15.

In the formula bar, it shows my cell as 4579637981576780

I need the exact number stored. This is for credit card numbers (I made up the example, don’t worry) and rounding is a BIG problem.

I know that you can put a tic (‘) in front of the number to format it as text, but then my users can’t copy/paste the value into our billing software unless they want to delete the pasted tic.

They are lazy and won’t do this.

How can I keep the format of my number but make it copy/paste-able?

Thanks!!

Reply | Quote

Replies

Excel can ONLY handle 15 NUMBERS since that is ALL the precision it has for numbers. It will ALWAYS truncate the LAST digit if more than 15 are used!

You must either make it TEXT, with the apostrophe (‘) or a space( ), or use combinations of 2 numbers to get 16 digits.
(8 + 8, 12+4, etc)

Steve

Reply | Quote

Thank you for the reply.

I was afraid of something like that. I am going to try putting a letter at the end of the credit card number, 4579637981576789X, for example, and see if when they paste it, the X is allowed or not. It is my hope that the billing system is trapping for only 16 digits and lets the CC number in but not the X.

Thanks again!

Reply | Quote

I tried something else that also seems to work:

Format the ENTIRE column(s) to TEXT (select entire column – format – cells – number tab TEXT)
Now you should be able to enter “numbers” but excel will keep it as text, though there is no apostrophe needed!

Just make sure if someone gets the Scientific notation (x.xxE+15) that they check the format, since it is now NUMERIC and the last digit is lost!

Steve

Reply | Quote

If you format the source cells as text, and the copy and paste is done default, the format gets carried over, so there shouldn’t be any problem. (Formatting cells as text does cause some other minor hassles.)

Reply | Quote

If you don’t mind using another column, how about this…
Have the users enter the credit card numbers including hard spaces, i.e.,

4579 6379 8157 6789

in your example. Have a formula in another column:

=SUBSTITUTE(A1," ","")

The SUBSTITUTE would make it text, although you’d have to check that it’s still text after pasting. A side benefit would be that data entry errors might be reduced in that it’s easier to spot mistakes in the spaced format than if all 16 digits are shown consecutively.

Reply | Quote