[Solved] Very long numbers paste incorrectly

[NTcalc]

Hello. I'm making a spreadsheet, and in it, I'm pasting strings of numbers that need to be what they are exactly, however when I paste them, they are different.

Whenever I copy, and then paste a number of a certain length, the number is incorrect.

I copy "1005003178476724", and then when I paste into a cell, it gets changed into "1005003178476720", or "1.00500317847672E+015". I've tried changing the cell number format, and making it TEXT works, but if I want to do math with it, it changes the number to something wrong again. Example (using basic math as an example):

Code: Select all

   A                 | B
1  1005003178476724  | (=A1+3) 1005003178476730

Inputting before pressing enter:   https://i.ibb.co/KbnBFcY/number.png

Edit: 2024-06-23: Broken link -- MrProgrammer, forum moderator

After pressing enter:   https://i.ibb.co/7Nc4yHx/number2.png

Edit: 2024-06-23: Broken link -- MrProgrammer, forum moderator

Any help would be appreciated.

Edit: Changed subject, was Pasting the wrong numbers  Make your post understandable by others  -- MrProgrammer, forum moderator

[MrProgrammer]

I copy "1005003178476724", and then when I paste into a cell, it gets changed into "1005003178476720", or "1.00500317847672E+015". I've tried changing the cell number format, and making it TEXT works, but if I want to do math with it, it changes the number to something wrong again.

If you need to perform exact calculations with more than 15 significant digits, a spreadsheet is the wrong tool for you.
[Solved] Unfortunate rounding on large numbers
[Solved] How to change the zeros at the end of long numbers
[Solved] Inaccuracy for big power function
[Solved] Problem creating the Fibonacci series
[Solved] Is there any support for big integers?

I'm pasting strings of numbers that need to be what they are exactly, however when I paste them, they are different.

If you format the cell as text before pasting, the string of digits will be stored exactly as it is on the clipboard. This is what one should use for postal codes, or identifiers of various types like product keys, the ISBN in library science, codes from banknotes, vehicle identification numbers, identifiers used in network protocols, etc. However these are values which will not be used for calculations. One doesn't add postal codes or take their square roots. You should not expect Calc to perform arithmetic with text values.


If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[NTcalc]

Ah, I see. Current technology doesn't support big numbers, I guess. I mean, Calc isn't really meant for massive calculations, despite being called "Calc". At least it's intentional, and not a bug. Thanks, though.

[MrProgrammer]

Current technology doesn't support big numbers, I guess.

Of course it does. The IBM 702 (a vacuum-tube machine) of the mid-1950s could do arithmetic with 500-digit numbers. I used a machine in the 1970s (IBM 1620) which could do that as well. Today, there is plenty of software available which will do calculuations with very, very long numbers: List of arbitrary-precision arithmetic software. And here on my Mac I can easily calculate 37³⁷ with the pre-installed programs (underline for what I have typed):

Last login: Sun Nov 20 00:07:47 on ttys000
$ bc -q
37^37
10555134955777783414078330085995832946127396083370199442517
quit

The answer is returned instantly. It takes bc about 15 seconds to calculate the 695975 digits of

202211200812.png (19.9 KiB) Viewed 4384 times

First 32 and last 32: 37598235267837885389221309308959 … 32933502849310357073612870132343

A spreadsheet is not a good tool for calculations like these.

Calc isn't really meant for massive calculations, despite being called "Calc".

Calc does the calculations that most people need, but it doesn't do everything. If you have unusual requirements, you need special software (list above).

[mahairod]

Ah, I see. Current technology doesn't support big numbers, I guess. I mean, Calc isn't really meant for massive calculations, despite being called "Calc". At least it's intentional, and not a bug. Thanks, though.

If you (or someone else) really need to make computations with big numbers in spreadsheets there is one chance for it. There is an application called BigNum Calcum for this special case. It supports computation with long numbers with precision up to 64-bits or roughly 19 decimal digits. I've made it by the same reason as you asked here on a forum: I needed to calculate 64-bit memory addresses and found there are no appropriate functionality in any of the known products.
The next version is supposed to handle opening Calc ODS file format. It's almost ready yet some pre-release checks are needed.

Edit: As of June 2024, this product runs only on Windows. It supports integers, text, and simple formulas, but not floating point numbers. Integers can be up to 19 digits decimal or 16 digits hexadecimal. Formulas support numeric +, -, *, /, %, shifts << or >>, and text concatenation. Editing includes copy/cut/paste, row/column insert/delete, and CSV export/import. -- MrProgrammer, forum moderator