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
Kudotek v2.0 