The Salesforce 18 Digit ID

As a Salesforce.com consultant, I've run into this 18 digit vs. 15 digit ID issue many times when working in Salesforce.com. Here is why:

When working with data in salesforce.com there will be times when you need the 18 digit ID of a given record. This will most likely happen because you have two tables in Excel and you need to do a lookup from one table to the other using an ID.

Say for example you need a list of contacts at accounts where you have open opportunities. If not using Contact Roles, which is the case for many companies, in order to produce this list you have to make yourself a report of Accounts & Contacts and a report of Accounts & Opportunities - both brought into excel as separate sheets. If both reports have the Account ID on it, you would think you can simply do a vlookup from one excel sheet to the other to find matching accounts.

You can do this lookup, but you might be stumped to find out that many "hits" occur that weren't legitimate. Meaning, you "matched" an account that wasn't really a match.

The reason is that a Salesforce ID is a 15 digit CASE SENSITIVE string of characters. The excel vlookup function though ISN'T CASE SENSITIVE! Therefore, an ID that looks like this in SF:

0016000000MRatd

might match this id:

0016000000MRAtd

or this one:

0016000000MRaTd

or this one:

0016000000mRatd

and so on...

And I've been amazed to find out how many false hits occur. "What are the odds?", you might say to yourself. Higher than you think!

Salesforce.com gets around this by creating an 18 digit version of the ID. The last 3 digits of the 18 digit ID are all capital letters and each of those 3 is a checksum for the other digits in the 15 digit ID. The 16th digit is a checksum of the first 5 characters, the 17th checks the second 5, and the 18th checks the last 5. It's a good technique for ensuring an exact match.

In order to do a vlookup on IDs in excel, you MUST use the 18 digit IDs. The question is: how do you get the 18 digit ID?

For years I used an excel sheet I had written to calculate the checksum columns and return the 18 digit ID. (If you want that excel sheet, ask me for it and I'll send it to you) It worked fine, but was a drag to use. It finally occurred to me that there must be a formula I could create in a Salesforce custom formula field that would return the 18 digit ID. A quick google search revealed this formula. (Hat tip to Erik Mittmeyer who posted it on a developer board in Salesforce)

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

To put this formula to work, simply create a custom formula field that returns type text, place the code above in the formula field and voila, you've got an 18 digit ID that you can easily add to any report. Huge time saver.

If you need help implementing this or with any other salesforce.com issue you might face, feel free to contact me at www.snapptraffic.com. We can help you with any setup, customization, or development issue you might face in Salesforce.com.