Pages

Friday, March 23, 2018

Salesforce: Convert ID from 15 to 18 characters with Excel formula


CASESAFEID() will return 18 characters ID from original 15 characters ID, the main difference is: 15 characters ID is case sensitive, while 18 characters ID is case insensitive. The issue will arise when using 15 characters ID when your teams working in Excel for data analysis, VLOOKUP() of Excel will not work properly, as it is case insensitive, so the best is to use 18 characters ID which is case insensitive.

But, if you are not the system admin or do not have permission to create a custom field to use CASESAFEID() for case-insensitive 18 characters ID, you can copy and paste the 15 characters ID using this online tool from admin booster to produce the 18 characters ID.

The other option and this is very useful when you have a lot of rows in Excel that needs to convert the ID from 15 to 18 characters is to use Excel formula. The formula below is copied from Salesforce developer forum, just change A2 with the excel cell of 15 characters ID.

=CONCATENATE(A2,
MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",(
IFERROR(IF(FIND(MID(A2,1,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,1,0),0)
+IFERROR(IF(FIND(MID(A2,2,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,2,0),0)
+IFERROR(IF(FIND(MID(A2,3,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,4,0),0)
+IFERROR(IF(FIND(MID(A2,4,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,8,0),0)
+IFERROR(IF(FIND(MID(A2,5,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,16,0),0)
+1),1),
MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",(
IFERROR(IF(FIND(MID(A2,6,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,1,0),0)
+IFERROR(IF(FIND(MID(A2,7,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,2,0),0)
+IFERROR(IF(FIND(MID(A2,8,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,4,0),0)
+IFERROR(IF(FIND(MID(A2,9,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,8,0),0)
+IFERROR(IF(FIND(MID(A2,10,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,16,0),0)
+1),1),
MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",(
IFERROR(IF(FIND(MID(A2,11,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,1,0),0)
+IFERROR(IF(FIND(MID(A2,12,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,2,0),0)
+IFERROR(IF(FIND(MID(A2,13,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,4,0),0)
+IFERROR(IF(FIND(MID(A2,14,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,8,0),0)
+IFERROR(IF(FIND(MID(A2,15,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,16,0),0)
+1),1))


ReferenceFormula Field: calculating the 18-digit ID from the 15-digit ID



12 comments:

  1. Really helpful.... Great insight...

    ReplyDelete
  2. You can also use https://sfdcwisdom.com/salesforce-15-to-18-digit

    ReplyDelete
  3. I know this is an old post, but just in case it is useful to someone, if you want to do ad-hoc conversions of Id's, rather than programatically, then this Chrome extension makes it easy:
    https://chrome.google.com/webstore/detail/sf-15-to-18/cogllpmaoflgaekieefhmglbpgdgmoeg

    FYI - I'm the developer. Please use the feedback form on the app if you'd like to suggest any improvements or additional functionality.

    Thanks!

    ReplyDelete

Page-level ad