The CASESAFEID() function retrieves an 18-character ID from an original 15-character ID. The primary distinction is that the 15-character ID is case-sensitive, whereas the 18-character ID is case-insensitive. An issue can arise when using the 15-character ID in Excel for data analysis because Excel’s VLOOKUP() function is case-insensitive, leading to potential errors. Therefore, it’s preferable to use the 18-character ID, which is case-insensitive.
However, if you lack the necessary permissions to create a custom field or if you’re not the system administrator, you can still generate the 18-character ID by copying and pasting the 15-character ID into an online tool like the one provided by admin booster.
Another option, especially useful when dealing with numerous rows in Excel that require ID conversion from 15 to 18 characters, is to utilize an Excel formula. The formula below, sourced from the Salesforce developer forum, can be used by replacing “A2” with the Excel cell containing the 15-character ID:
=MID(A2, 1, 8) & “00” & MID(A2, 9, 4) & “000” & MID(A2, 13, 4) & “0000” & MID(A2, 17, 4) & “00000000”
=CONCATENATE(A2,<br>MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",(<br>IFERROR(IF(FIND(MID(A2,1,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,1,0),0)<br>+IFERROR(IF(FIND(MID(A2,2,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,2,0),0)<br>+IFERROR(IF(FIND(MID(A2,3,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,4,0),0)<br>+IFERROR(IF(FIND(MID(A2,4,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,8,0),0)<br>+IFERROR(IF(FIND(MID(A2,5,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,16,0),0)<br>+1),1),<br>MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",(<br>IFERROR(IF(FIND(MID(A2,6,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,1,0),0)<br>+IFERROR(IF(FIND(MID(A2,7,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,2,0),0)
+IFERROR(IF(FIND(MID(A2,8,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,4,0),0)<br>+IFERROR(IF(FIND(MID(A2,9,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,8,0),0)<br>+IFERROR(IF(FIND(MID(A2,10,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,16,0),0)<br>+1),1),<br>MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",(<br>IFERROR(IF(FIND(MID(A2,11,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,1,0),0)<br>+IFERROR(IF(FIND(MID(A2,12,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,2,0),0)<br>+IFERROR(IF(FIND(MID(A2,13,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,4,0),0)<br>+IFERROR(IF(FIND(MID(A2,14,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,8,0),0)<br>+IFERROR(IF(FIND(MID(A2,15,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,16,0),0)<br>+1),1))