15 Digit to 18 Digit Conversion formula

pbaba

Looking for some help with a formula to convert the Salesforce 15 digit ID to an 18 digit ID. The formula that was originally posted in the community does not work correctly and creates an error for some of the conversions on the 18th digit. Any assistance is greatly appreciated.

danilang

Hi @pbaba 

The funky formula that you're using has an error in it...well actually 15 errors.  The > near the end of each the lines needs to be replaced with >=  

iif(FindString("ABCDEFGHIJKLMNOPQRSTUVWXYZ", substring([5 Digit],0,1))>0,1,0)

becomes

iif(FindString("ABCDEFGHIJKLMNOPQRSTUVWXYZ", substring([5 Digit],0,1))>=0,1,0)

What each of these lines is doing is checking to see if the character at that position is a Letter, "A-Z".  If it is, then do some binary math based on the character position.  Each group of 5 characters is summed and used as an offset into the ABC...345 string, to give each of the 3 new characters.

The problem is that FindString returns -1 if the value isn't found.  Since Alteryx strings are enumerated starting at character position 0, the letter "A" in the each of the last group of 5 characters is missed, so the total for the last digit is off. 

The formulas were probably copied over from Excel, where the first character in a string is numbered 1 and not 0

The "new" column is the output from the attached workflow

Dan   

Corrected.yxmd