Tuesday, February 26, 2013

Inline 18 digit Salesforce ID Excel formula

If you have not got the ability to create a Salesforce.com custom field to generate the 18 digit case insensetive ID, as demonstrated here, you can use this formula to convert the ID in "A2" to the 18 digit ID. Of course you can then extend this formula all the way down column A if you need to.


=A2 & LOOKUP(IF(AND(CODE(RIGHT(LEFT($A2,5),1))>=65,CODE(RIGHT(LEFT($A2,5),1))<=90),1,0) & IF(AND(CODE(RIGHT(LEFT($A2,4),1))>=65,CODE(RIGHT(LEFT($A2,4),1))<=90),1,0) & IF(AND(CODE(RIGHT(LEFT($A2,3),1))>=65,CODE(RIGHT(LEFT($A2,3),1))<=90),1,0)& IF(AND(CODE(RIGHT(LEFT($A2,2),1))>=65,CODE(RIGHT(LEFT($A2,2),1))<=90),1,0)& IF(AND(CODE(LEFT($A2,1))>=65,CODE(LEFT($A2,1))<=90),1,0),{"00000","00001","00010","00011","00100","00101","00110","00111","01000","01001","01010","01011","01100","01101","01110","01111","10000","10001","10010","10011","10100","10101","10110","10111","11000","11001","11010","11011","11100","11101","11110","11111"},{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","0","1","2","3","4","5"}) & LOOKUP(IF(AND(CODE(RIGHT(LEFT($A2,10),1))>=65,CODE(RIGHT(LEFT($A2,10),1))<=90),1,0) & IF(AND(CODE(RIGHT(LEFT($A2,9),1))>=65,CODE(RIGHT(LEFT($A2,9),1))<=90),1,0) & IF(AND(CODE(RIGHT(LEFT($A2,8),1))>=65,CODE(RIGHT(LEFT($A2,8),1))<=90),1,0)& IF(AND(CODE(RIGHT(LEFT($A2,7),1))>=65,CODE(RIGHT(LEFT($A2,7),1))<=90),1,0)& IF(AND(CODE(RIGHT(LEFT($A2,6),1))>=65,CODE(RIGHT(LEFT($A2,6),1))<=90),1,0),{"00000","00001","00010","00011","00100","00101","00110","00111","01000","01001","01010","01011","01100","01101","01110","01111","10000","10001","10010","10011","10100","10101","10110","10111","11000","11001","11010","11011","11100","11101","11110","11111"},{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","0","1","2","3","4","5"})  & LOOKUP(IF(AND(CODE(RIGHT(LEFT($A2,15),1))>=65,CODE(RIGHT(LEFT($A2,15),1))<=90),1,0) & IF(AND(CODE(RIGHT(LEFT($A2,14),1))>=65,CODE(RIGHT(LEFT($A2,14),1))<=90),1,0) & IF(AND(CODE(RIGHT(LEFT($A2,13),1))>=65,CODE(RIGHT(LEFT($A2,13),1))<=90),1,0)& IF(AND(CODE(RIGHT(LEFT($A2,12),1))>=65,CODE(RIGHT(LEFT($A2,12),1))<=90),1,0)& IF(AND(CODE(RIGHT(LEFT($A2,11),1))>=65,CODE(RIGHT(LEFT($A2,11),1))<=90),1,0),{"00000","00001","00010","00011","00100","00101","00110","00111","01000","01001","01010","01011","01100","01101","01110","01111","10000","10001","10010","10011","10100","10101","10110","10111","11000","11001","11010","11011","11100","11101","11110","11111"},{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","0","1","2","3","4","5"})