Working with case sensitive Salesforce Account IDs in Excel

If you manipulate data in excel and want to use the salesforce ID field value as a unique key to move and match data between work sheets then you will quickly discover that normal vlookups can cause huge problems…

1. Salesforce record IDs are case sensitive.
2. Excel vlookups are not case sensitive.

We can get around this by encoding each salesforce ID in to a number string.

To achieve this:

1. Create a ‘helper column’ to the left of the Salesforce ID column in every sheet you are working in.
2. Encode each character of every Salesforce ID in to a number by using the following formula in the ‘helper column’ (assuming the ‘helper column’ is A and the Salesforce ID is in column B):

=CODE(MID(B2,1,1))&CODE(MID(B2,2,1))&CODE(MID(B2,3,1))&CODE(MID(B2,4,1))&CODE(MID(B2,5,1))&CODE(MID(B2,6,1))&CODE(MID(B2,7,1))&CODE(MID(B2,8,1))&CODE(MID(B2,9,1))&CODE(MID(B2,10,1))&CODE(MID(B2,11,1))&CODE(MID(B2,12,1))&CODE(MID(B2,13,1))&CODE(MID(B2,14,1))&CODE(MID(B2,15,1))

3. The above formula will encode each of the 15 characters in your Salesforce IDs in to a unique number in the ‘helper column’. You can now use this unique number as the key in your vlookup formulas.