2

I'm struggling with Luhn algorithm to calculate check digit for 14 digit length number. I found this solution here, hut it's only for 6 digit number. If I want to create it for more digits I'll get over formula compiled characters limit.

Here's formula for 6 digit number

MOD(10 - MOD(
         IF(
               MOD(VALUE(wir_GSAP_Inv_No__c) ,10)*2 > 9,
               MOD(MOD(VALUE(wir_GSAP_Inv_No__c) ,10)*2,10) + 1,
               MOD(VALUE(wir_GSAP_Inv_No__c) ,10)*2
           ) +
           FLOOR(MOD(MOD(VALUE(wir_GSAP_Inv_No__c), 100)/10,10)) +
           IF(
               (FLOOR(MOD(VALUE(wir_GSAP_Inv_No__c), 1000)/100)*2)>9,
               MOD(FLOOR(MOD(VALUE(wir_GSAP_Inv_No__c), 1000)/100)*2,10)+1,
               (FLOOR(MOD(VALUE(wir_GSAP_Inv_No__c), 1000)/100)*2)
           ) +
           FLOOR(MOD(VALUE(wir_GSAP_Inv_No__c), 10000)/1000) +
           IF(
               (FLOOR(MOD(VALUE(wir_GSAP_Inv_No__c), 1000)/100)*2)>9,
               MOD(FLOOR(MOD(VALUE(wir_GSAP_Inv_No__c), 1000)/100)*2,10)+1,
               (FLOOR(MOD(VALUE(wir_GSAP_Inv_No__c), 1000)/100)*2)
           ) +
           FLOOR(MOD(VALUE(wir_GSAP_Inv_No__c), 10000)/1000)
       ,10)
  ,10)

Any idea how can I do it?

Luhn algorithm (from wikipedia): The formula verifies a number against its included check digit, which is usually appended to a partial account number to generate the full account number. This number must pass the following test:

  1. From the rightmost digit, which is the check digit, moving left, double the value of every second digit; if the product of this doubling operation is greater than 9 (e.g., 8 × 2 = 16), then sum the digits of the products (e.g., 16: 1 + 6 = 7, 18: 1 + 8 = 9).
  2. Take the sum of all the digits.
  3. If the total modulo 10 is equal to 0 (if the total ends in zero) then the number is valid according to the Luhn formula; else it is not valid. Assume an example of an account number "7992739871" that will have a check digit added, making it of the form 7992739871x.

The check digit (x) is obtained by computing the sum of digits then computing 9 times that value modulo 10 (in equation form, (67 × 9 mod 10)). In algorithm form:

  1. Compute the sum of the digits (67).
  2. Multiply by 9 (603).
  3. The last digit, 3, is the check digit. Thus, x=3.
Improve this question

2 Answers 2

Reset to default
2

The type of the field must be "Text" in order to use the MID function. Can you check that one and tell me if it works ?

MOD( 

VALUE(MID(wir_GSAP_Inv_No__c, 14, 1))+ 

IF( VALUE(MID(wir_GSAP_Inv_No__c, 13, 1))<5, 
VALUE(MID(wir_GSAP_Inv_No__c, 13, 1))*2, 
VALUE(MID(TEXT(VALUE(MID(wir_GSAP_Inv_No__c, 13, 1))*2),1,1))+VALUE(MID(TEXT(VALUE(MID(wir_GSAP_Inv_No__c, 13, 1))*2),2,1)) 
) + 

VALUE(MID(wir_GSAP_Inv_No__c, 12, 1))+ 

IF( VALUE(MID(wir_GSAP_Inv_No__c, 11, 1))<5, 
VALUE(MID(wir_GSAP_Inv_No__c, 11, 1))*2, 
VALUE(MID(TEXT(VALUE(MID(wir_GSAP_Inv_No__c, 11, 1))*2),1,1))+VALUE(MID(TEXT(VALUE(MID(wir_GSAP_Inv_No__c, 11, 1))*2),2,1)) 
) + 

VALUE(MID(wir_GSAP_Inv_No__c, 10, 1))+ 

IF( VALUE(MID(wir_GSAP_Inv_No__c, 9, 1))<5, 
VALUE(MID(wir_GSAP_Inv_No__c, 9, 1))*2, 
VALUE(MID(TEXT(VALUE(MID(wir_GSAP_Inv_No__c, 9, 1))*2),1,1))+VALUE(MID(TEXT(VALUE(MID(wir_GSAP_Inv_No__c, 9, 1))*2),2,1)) 
) + 

VALUE(MID(wir_GSAP_Inv_No__c, 8, 1))+ 

IF( VALUE(MID(wir_GSAP_Inv_No__c, 7, 1))<5, 
VALUE(MID(wir_GSAP_Inv_No__c, 7, 1))*2, 
VALUE(MID(TEXT(VALUE(MID(wir_GSAP_Inv_No__c, 7, 1))*2),1,1))+VALUE(MID(TEXT(VALUE(MID(wir_GSAP_Inv_No__c, 7, 1))*2),2,1)) 
) + 

VALUE(MID(wir_GSAP_Inv_No__c, 6, 1))+ 

IF( VALUE(MID(wir_GSAP_Inv_No__c, 5, 1))<5, 
VALUE(MID(wir_GSAP_Inv_No__c, 5, 1))*2, 
VALUE(MID(TEXT(VALUE(MID(wir_GSAP_Inv_No__c, 5, 1))*2),1,1))+VALUE(MID(TEXT(VALUE(MID(wir_GSAP_Inv_No__c, 5, 1))*2),2,1)) 
) + 

VALUE(MID(wir_GSAP_Inv_No__c, 4, 1))+ 

IF( VALUE(MID(wir_GSAP_Inv_No__c, 3, 1))<5, 
VALUE(MID(wir_GSAP_Inv_No__c, 3, 1))*2, 
VALUE(MID(TEXT(VALUE(MID(wir_GSAP_Inv_No__c, 3, 1))*2),1,1))+VALUE(MID(TEXT(VALUE(MID(wir_GSAP_Inv_No__c, 3, 1))*2),2,1)) 
) + 

VALUE(MID(wir_GSAP_Inv_No__c, 2, 1))+ 

IF( VALUE(MID(wir_GSAP_Inv_No__c, 1, 1))<5, 
VALUE(MID(wir_GSAP_Inv_No__c, 1, 1))*2, 
VALUE(MID(TEXT(VALUE(MID(wir_GSAP_Inv_No__c, 1, 1))*2),1,1))+VALUE(MID(TEXT(VALUE(MID(wir_GSAP_Inv_No__c, 1, 1))*2),2,1)) 
) 

, 10) != 0

It works for me, and the validation rule is way longer than that because I check if it is a number with ISNUMBER, if the length is 14, the country, etc. Si I don't think you are going to hit the length limit (I may be mistaken though, because my field name is shorter than yours)

Improve this answer
1
  • Thanks for answer, but I get an error: Error: Formula result is data type (Boolean), incompatible with expected data type (Number). As I understand the code, you are just checking if the the number is Mod 10 valid, but I actually need to calculate the check digit.
    – mascot
    Commented Jun 16, 2015 at 6:45
1

What I have below returns the checksum digit for a 14 digit number stored in a text field.

Please note that as written, it only works for values that are exactly 14 digit numbers, but it should be easy enough to adapt for other sized numbers.

VALUE (
RIGHT (
TEXT (
9
*
(
IF ( 
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 2 ) , 1 ) ) * 2 < 10 
 , 
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 2 ) , 1 ) ) * 2 
 , 
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 2 ) , 1 ) ) * 2 - 9
)
 +
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 3 ) , 1 ) ) * 1
 +
IF ( 
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 4 ) , 1 ) ) * 2 < 10
,
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 4 ) , 1 ) ) * 2
,
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 4 ) , 1 ) ) * 2 - 9
)
 +
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 5 ) , 1 ) ) * 1
 +
IF ( 
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 6 ) , 1 ) ) * 2 < 10
,
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 6 ) , 1 ) ) * 2
,
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 6 ) , 1 ) ) * 2 - 9
)
 +
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 7 ) , 1 ) ) * 1
 +
IF ( 
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 8 ) , 1 ) ) * 2 < 10
,
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 8 ) , 1 ) ) * 2
,
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 8 ) , 1 ) ) * 2 - 9
)
 +
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 9 ) , 1 ) ) * 1
 +
IF ( 
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 10 ) , 1 ) ) * 2 < 10
,
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 10 ) , 1 ) ) * 2
,
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 10 ) , 1 ) ) * 2 - 9
)
 +
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 11 ) , 1 ) ) * 1
 +
IF ( 
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 12 ) , 1 ) ) * 2 < 10
,
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 12 ) , 1 ) ) * 2
,
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 12 ) , 1 ) ) * 2 - 9
)
 +
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 13 ) , 1 ) ) * 1
 +
IF ( 
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 14 ) , 1 ) ) * 2 < 10
,
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 14 ) , 1 ) ) * 2
,
VALUE ( LEFT ( RIGHT ( analysis__X14DigitNumber__c , 14 ) , 1 ) ) * 2 - 9
)

) 
)
,
1
)
)
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .