0

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?

1
  • 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
    Oct 10 at 0:00
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.