Internal record IDs in Salesforce CRM, as found in all custom and standard objects such as the Opportunity, Account, Contact, and so on contain 15-character text-based values.

The text values can be described as a base-62 number, as each of the individual 15 characters can be either a numeric digit (in the range 0-9), a lowercase letter (in the range a-z), or an uppercase letter (in the range A-Z).

These 15-character Salesforce values are therefore case-sensitive, since there can be two unique IDs that owe their uniqueness to the fact that they have a character or characters that differ only in case. For example, 100000000000ABC is different to 100000000000abc.

However, there are applications such as Microsoft Excel, which are not case-sensitive and they do not recognize the difference between the ID 100000000000ABC and the ID 100000000000abc. This results in features such as the Excel formula VLOOKUP, failing to match correct records from an exported Salesforce report.

Salesforce realized the potential problem where the case of the source lookup value is not processed correctly within external applications that are not case-aware.

To provide better compatibility between Salesforce and tools such as MS Excel, MS Access, and SQL Server, Salesforce has established an 18-character case-insensitive ID that is formed by adding a suffix to the 15-character ID, a method that respects the case-insensitive nature of these tools.

In this recipe, we will convert a 15-character case-sensitive ID to an 18-character case-insensitive ID.