=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"})
Tuesday, February 26, 2013
Inline 18 digit Salesforce ID Excel formula
Posted by
Fosnez
around
10:21 AM
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.
Labels:
Code,
Salesforce.com
Subscribe to:
Post Comments (Atom)
3 comments:
Nice Post
Thank you for posting this - I come to it a few times a week!
You rock, my old copy of this formula was corrupted. Glad someone is posting it out there for us common people.
Post a Comment
Note: Only a member of this blog may post a comment.