Adding more than 15 digits in Excel

Question

I want to add more than 20 digits in an Excel cell.

The current format of the cell is general, it converts the number to an exponential format. I tried with a number format and accounting, but when I enter more than 15 digits it gets converted to 0's.

What are the recommended steps for stopping Excel from converting data to exponential Format for 20 digits when in the general format?

Example: 12345678901234567890

Excel converts it to 1.23457E+19 in general format.

Without using ' before the value, is there any other way to keep value same?

Answer 1

Some numbers are displayed in exponential format if the column is too narrow and you can fix this by increasing the width of the column.

However, very large numbers in Excel are stored in floating point format and cannot be represented in Excel exactly. You may be able to override their display using cell formatting but the true values will still be stored and processed using floating point arithmetic, with its inherent limitations.

If you need to perform calculations with greater precision you need an application that supports arbitrary precision arithmetic.

Answer 2

When you don't need to calculate with these numbers, you can treat them as text, see the other answers. When you need to calculate with them, then it becomes difficult because Excel has only 15 siginificant digits. The possibilities I know are:

1. Split the number in 2 (or more) cells. Put a part of the number in one cell and the remainder in another cell. The consequence is that you need to develop your own formules for calculations.

2. Another solution is using add-in's. For a free one study the articles mentioned in http://www.excel-ticker.com/calculation-of-very-large-numbers-in-excel-part-5-add-in/

Answer 3

The reason is the limited precision that can be stored in a floating point variable. For a complete explanation you should read the paper "What Every Computer Scientist Should Know About Floating-Point Arithmetic", by David Goldberg, published in the March, 1991 issue of Computing Surveys.

Another, more accessible site, is Chip Pearson's site.

In Excel, the floating point type is Double which is a IEEE 64-bit (8-byte) floating-point number. These can display 15 digit precision (well sort of, see the MSDN article Excel Worksheet and Expression Evaluation).

Answer 4

I had the same issue with barcodes that I was pulling from a database, the solution that worked best for me is to pass the number to excel as a formula that displays text.

For example, instead of passing just the barcode, I was passing ="barcode" so that excel interprets as a string a shows the whole number without any other characters as in the ' solution. This way you can copy and paste the number easily.

Here's my SQL function:

ALTER function [dbo].[ConvertBarcode](@BARCODE varchar(40))
returns varchar(40)
as
begin
return '="'+@BARCODE+'"'
end

Happy coding!

Answer 5

You can correctly store the 15 most significant digits of an integer in Excel (my version is Excel 2010).

If you enter 999,999,999,999,999 in a cell and add 1 to it in the cell below it correctly shows 1,000,000,000,000,000, but try to add another 1 and you get 1,000,000,000,000,000 again.

1,000,000,000,000,000 to 1,000,000,000,000,005 show as 1,000,000,000,000,000 1,000,000,000,000,006 to 1,000,000,000,000,015 show as 1,000,000,000,000,010 1,000,000,000,000,016 to 1,000,000,000,000,025 show as 1,000,000,000,000,020

Which is what you would expect with the midpoint rounding down.

Answer 6

1. just copy your number from the cell
2. format another cell(where to copy-destination cell) as text
3. double click on the destination cell and paste in it.

Answer 7

I had to have a large number in order to create a unique number for a website.

In the end I created the large numbers by using concatenate to add cells together. Example:

OL  2890000000  0000004 OL28900000000000004
OL  2890000000  0000005 OL28900000000000005
OL  2890000000  0000006 OL28900000000000006
OL  2890000000  0000007 OL28900000000000007
OL  2890000000  0000008 OL28900000000000008

Answer 8

You might be able to solve the issue with these two steps:

1) Seperate the numbers into as many columns as necessary. Make sure the format is TEXT.

2) Use FORMULA>TEXT>CONCATENATE to combine the columns into one column and you should get your desired result.

In any case that you need to do so for running numbers like serial numbers for example, seperate the numbers and run them down in GENERAL format first, then combine.

Answer 9

In order to add more than 14 digits to an Excel cell, follow these steps:

1. Copy the data which has such numbers;
2. Paste it in a Notepad and click "Save As" and name the .txt file;
3. Open an excel sheet, Go to Data and click on "From Txt/Csv". (Refer to the picture in the link);
4. Select the .txt file that was saved;
5. A pop will appear and in that dialogue box click on "Load" button.

Answer 10

Or you could choose one number that is common with all the cells and replace it with a letter that isn't already present. Copy paste into Excel remove duplicates and sort before copying and pasting back in Word where you replace the letter back to the common number for example:

1300000000029577 13z0000000029577

1300000000029578 13z0000000029578

1300000000034748 13z0000000034748

1300000000086943 13z0000000086943

Answer 11

If you are just entering information without having to do any math or calculations, format the cell as 'text'. Not sure why it won't work in the number format.