Convert 15-Character to 18-Character IDs in Spreadsheets without Code

Q: “How do I convert a 15-character Salesforce ID into an 18-character ID in a spreadsheet like Excel or Google Sheets, without code?”

A: Use the formula below. This formula assumes your 15-character ID is in cell A2.

=A2 & 
MID( 
    "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345", 
    AND(CODE(MID(A2,5,1))>64,CODE(MID(A2,5,1))<91) * 16 + 
    AND(CODE(MID(A2,4,1))>64,CODE(MID(A2,4,1))<91) * 8 + 
    AND(CODE(MID(A2,3,1))>64,CODE(MID(A2,3,1))<91) * 4 + 
    AND(CODE(MID(A2,2,1))>64,CODE(MID(A2,2,1))<91) * 2 + 
    AND(CODE(MID(A2,1,1))>64,CODE(MID(A2,1,1))<91) * 1 + 1, 
    1) & 
MID( 
    "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345", 
    AND(CODE(MID(A2,10,1))>64,CODE(MID(A2,10,1))<91) * 16 + 
    AND(CODE(MID(A2,9,1))>64,CODE(MID(A2,9,1))<91) * 8 + 
    AND(CODE(MID(A2,8,1))>64,CODE(MID(A2,8,1))<91) * 4 + 
    AND(CODE(MID(A2,7,1))>64,CODE(MID(A2,7,1))<91) * 2 + 
    AND(CODE(MID(A2,6,1))>64,CODE(MID(A2,6,1))<91) * 1 + 1, 
    1) & 
MID( 
    "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345", 
    AND(CODE(MID(A2,15,1))>64,CODE(MID(A2,15,1))<91) * 16 + 
    AND(CODE(MID(A2,14,1))>64,CODE(MID(A2,14,1))<91) * 8 + 
    AND(CODE(MID(A2,13,1))>64,CODE(MID(A2,13,1))<91) * 4 + 
    AND(CODE(MID(A2,12,1))>64,CODE(MID(A2,12,1))<91) * 2 + 
    AND(CODE(MID(A2,11,1))>64,CODE(MID(A2,11,1))<91) * 1 + 1, 
    1)

If you’re curious how this logic works, you can check out Alan Shanahan‘s wonderful original post about it from 2010.

And if for some reason you’re allergic to the Salesforce formula function CASESAFEID(), you can use the logic I created in this blog post from 2011.

And while I’m at it, I recommend everyone makes a copy of this wonderful Field Utilities for Salesforce Administrators, Architects, and Developers spreadsheet for tools that can help you

  • change 15-character IDs to 18-character IDs
  • convert Field UI Labels (with characters and spaces) into Field API Names (with underscores)
  • generate XML for creating new fields and
  • fix the capitalization of broken 18-character IDs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s