# Luhn algorithm check digit in formula field

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.

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)

• 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 Jun 16 '15 at 6:45

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
)
)
``````