Calculating the modulo of large numbers in Google Spreadsheets

Question

For an IBAN Validation, I need to calculate the modulo of a very large number. What I am doing is simply

Example:

with A1 = 210501700012345678131468

=mod(A1;97)

The result should be 1. Instead it result in #NUM

Obviously my value in A1 is too large for sheets. How can I "help" sheets?

Hi and welcome. I suggest that you research more widely to build your IBAN validation since modulo alone is not going to cut it. According to Google Sheets not processing very large numbers (webapps by the ubiquitous @Rubén) fifteen digits are recognized as number but sixteen digits are recognised as text. There's an Apps script IBAN validator on gsuitefr.blogspot.com worth looking at - its modulo takes the IBAN as a string and processes it accordingly. — Tedinoz, Commented Oct 10, 2021 at 0:00

Erik Tyler · Accepted Answer · 2021-10-10 03:10:07Z

2

If all of your number-strings are 24 digits/characters in length, you should be able to use this:

=MOD( (MOD(LEFT(A2,12),97) * MOD(10^12,97)) + MOD(RIGHT(A2,12),97) ,97)

If your divisor will change, you can substitute each instance of 97 with a cell reference and then simply change the cell reference.

answered Oct 10, 2021 at 3:10

Erik Tyler

5,1232 gold badges8 silver badges11 bronze badges

Add a comment |

1 Answer 1