Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I need to be using a checksum to validate IBAN numbers as they are exported to a csv file ready for sending.
    However, the number ends up at 28 digits long (for UK IBAN size).

    The checksum is to have a mod 97 remainder of 1
    Typically this could be: 3214282912345698765432161182 mod 97 = 1

    I am having difficulty trying to incorporate this in Excel.
    Each time I use the value (in sheet) it is being truncated to 3.21428E+27
    Using in VBA I get 3.2142829123457E+27

    Any pointers please?

    TIA
    Alan
    Cheshire
    cloudy, 14C, showers

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Excel uses 15 significant digits for calculations - it cannot handle numbers with 28 digits without losing a lot of information.
    You'd have to store the IBAN numbers as text and program the calculations yourself, or use an add-in such as xlPrecision.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='780975' date='22-Jun-2009 12:26']Excel uses 15 significant digits for calculations - it cannot handle numbers with 28 digits without losing a lot of information.
    You'd have to store the IBAN numbers as text and program the calculations yourself, or use an add-in such as xlPrecision.[/quote]

    Many thanks for that Hans.
    Until now it hasn't been a problem as we use independent app for IBAN

    Using the cell value have managed to run checksum using good ol'fashioned long division iteration.
    myCheck = 97
    mylen = Len(ActiveCell.Value)
    For i = 1 To mylen
    myrem = (myrem & Mid(ActiveCell.Value, i, 1)) Mod myCheck
    Next i

    'use myRem to post any non 1 errors

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Alan

    In addition to what Hans states even if you could get it to work the MOD function returns an error if the divisor multiplied by 134,217,728 is less than or equal to the number being evaluated.

    It looks like your routine is probably the easiest at the moment
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •