Salesforce CRM user?

Are you a Salesforce CRM user?  Use Microsoft Excel for analysis or reports?  Then you’ll have met the Salesforce 15 digit ID problem.

Internally Salesforce IDs are 18 digits long.  But, Salesforce Reports reduce them to 15 digits.  So, for example, an Organisation ID internally might be 0014000000LmabcAAB, but in a report it will appear as 0014000000Lmabc. The missing last three digits (the “AAB” in the example) are check-digits; used for error detection.

Now that wouldn’t be a problem if it wasn’t that Windows applications, such as Excel and Access, are case insensitive.  The 15 digit IDs are unique, which is what you need when you use the Excel function like VLOOKUP, but Excel treats 0014000000Lmabc just the same as 0014000000LMABC (case insensitive!) - so, your Salesforce IDs are no longer unique and VLOOKUP will no long find the correct match … (incidentally, the 18 digit IDs are unique to VLOOKUP!)

There are algorithms you can use to reconstruct the last three digits of your Salesforce IDs (search Salesforce Help for “How can one convert a 15 character id to a 18 character id?“), but better still there’s a script that works in Google Docs online spreadsheet.

Convert 15 to 18 Digit Salesforce IDs with Google Spreadsheets [video] – enjoy:

My thanks to David Engel, of The Engel Journal, for his Post and Video on this topic.  Similarly, David’s acknowledges in his post Damon Douglas, David Padbury, and Stefan Kuehlechner.

If you need further support processing you Salesforce data using Microsoft Excel, or Access, then please don’t hesitate contact me.  Alternatively, there’s a Java script that converts Salesforce 15 digit IDs to 18 digits that can be found here.