Can you find a better way?

Since this is the procrastination corner, a little challenge for all who like puzzles :thinking::thinking::thinking::

In one of my docs I implemented an IBAN-checker.
However, my solution is rather unaesthetic and over complicated. and I am sure others could do it better.

If you want to have a little challenge, give it a shot:

Validating the IBAN
An IBAN is validated by converting it into an integer and performing a basic mod-97 operation (as described in ISO 7064) on it. If the IBAN is valid, the remainder equals 1.[Note 1] The algorithm of IBAN validation is as follows:[8]

  1. Check that the total IBAN length is correct as per the country. If not, the IBAN is invalid
  2. Move the four initial characters to the end of the string
  3. Replace each letter in the string with two digits, thereby expanding the string, where A = 10, B = 11, …, Z = 35
  4. Interpret the string as a decimal integer and compute the remainder of that number on division by 97
    If the remainder is 1, the check digit test is passed and the IBAN might be valid.

Example (fictitious United Kingdom bank, sort code 12-34-56, account number 98765432):

• IBAN: GB82 WEST 1234 5698 7654 32
• Rearrange: W E S T12345698765432 G B82
• Convert to integer: 3214282912345698765432161182
• Compute remainder: 3214282912345698765432161182 mod 97 = 1

For simplicity, I suggest you just use a simple version for test 1:

first 2 characters must be letters, and up to 34 alphanumerical characters in total.

1 Like

Can you share your current solution? I’m not sure if one can “simplify” this much…

Its rather bulky and requires an additional table and three columns in the main one.

Whats your email, I’ll invite you to it.

I thought of this like a challenge for those who want to try some fancier real-life puzzles :wink:

I think I have a solution but had to build a workaround for taking a remainder of a large integer - did you find a way to avoid that?

That is/was a pain in the butt.
Since remainder appears not to work with the size of these numbers (googlesheets doesnt do this either by the way), I have a separate table which looks at the length of the number, then subtracts 97^(lengthOfOurRemainingNumber-2) from it as long as possible.

As I said, its an ugly solution which I have :smiley: