How to divide really big numbers in Google Sheets

Question

As part of the creation of an IBAN number generator, I need to divide a 26-digit number by the number 97 to find out the check digits in the IBAN number.
After about an hour of searching, I found out that Google Sheets cannot calculate with such large numbers - Google Sheets not processing very large numbers
But how to do it?
How to bypass this limitation in Google Sheets?
I am trying to find the first 6 digits after the decimal point after dividing a 26 digit number by 97.

I have the same problem in LibreOffice Calc.


I am not looking for a way to verify the validity of the entire IBAN number.
I am looking for a way to create check digits when generating an IBAN.

Answer 1

The way to bypass the limitation is to perform the modulo calculation in smaller chunks that do not bump into the limitation.

For example, try the following:

1. Store the 26-digit number as a text string.
2. Split that text string into 4 sub-strings of 7 characters each (only 5 for the last one). For example:

   Text:    "81009029810000571856282062"
   Becomes: {"8100902","9810000","5718562","82062"}
   

3. Perform the modulo operation on the first substring then prepend the result of that operation to the next substring before again performing the modulo operation, and then prepend that result to the next substring before again performing the modulo, and then prepend that result to the last substring and perform the last modulo operation to see if the result is 1.

This works for the following reasons:

1. Sheets allows you to store a 26-digit number as a text string.
2. Sheets tries to interpret a text string as a number when used in a math calculation
3. Sheets will interpret a number as a text string when used with a text operator (line &).

Sample Formula

• txt is "81009029810000571856282062"

=REDUCE(,INDEX(MID(txt,{1,8,15,22},7)), LAMBDA(a,v, MOD(a&v,97)))
=1

Equivalent to:

=MOD( MOD( MOD( MOD( MID(txt,1,7), 97)& MID(txt,8,7), 97)& MID(txt,15,7), 97)& MID(txt,22,7), 97)
=1

Answer 2

After watching a few videos about division, specifically long division, I thought of simulating this process in Google Spreadsheets / LibreOffice.
I divided the 26-digit number into individual digits - I got 26 cells, and there was one number in each.
Then I used the mod() function on the first number from the left (from the top) and wrote the result in the next cell.
Then I multiplied this result by 10 and added it to the next digit in the sequence.
I used mod() again for this sum.
I repeated this until I reached the last digit in the row.
As a result of the last operation, I got a number that I just need to divide by the number 97 and I will get the numbers after the decimal point that I can use in the next steps to find out the control numbers in the IBAN number.
I checked the result from the last step in WolframAlpha and it looks like it gives good results.