We all know that each record in Salesforce is automatically assigned to a unique identifier as a Record ID. This is very handy, since we do not need to worry about manually creating the primary key.

If you are new to Salesforce, you will soon discover that there are two types of Record ID: the 15-character CamelCase Record ID and the 18-character Non-CamelCase Record ID. In most cases, you will see the 18-character Record ID.  For instance, when you open the record in a browser or you download the record data using the Salesforce Data Loader, you are going to see the 18-character Record ID.  However, when you pull the Record ID using Salesforce Report, it shows the 15-character Record ID instead. This becomes a challenge when you try to use Excel to perform VLOOKUP operations between Salesforce Report and Salesforce Data Loader data.  

‍

The good news is that there are ways to convert the 15-character Record ID to the 18-character type. The two most common approaches are 1) Using Excel formula or 2) Using Salesforce formula field.  Each approach has its advantages and disadvantages. 

‍

Let’s discuss.

‍

1. Using Excel Formula

Let’s assume that you download a Salesforce report to an Excel file, and it has the 15-character Record ID that you need to convert the 18-character Salesforce ID in cell A2. You can simply put the formula below in a new cell: 

‍

=CONCATENATE(A2, MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",(

IFERROR(IF(FIND(MID(A2,1,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,1,0),0)

+IFERROR(IF(FIND(MID(A2,2,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,2,0),0)

+IFERROR(IF(FIND(MID(A2,3,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,4,0),0)

+IFERROR(IF(FIND(MID(A2,4,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,8,0),0)

+IFERROR(IF(FIND(MID(A2,5,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,16,0),0)

+1),1),

‍

MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",(

IFERROR(IF(FIND(MID(A2,6,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,1,0),0)

+IFERROR(IF(FIND(MID(A2,7,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,2,0),0)

+IFERROR(IF(FIND(MID(A2,8,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,4,0),0)

+IFERROR(IF(FIND(MID(A2,9,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,8,0),0)

+IFERROR(IF(FIND(MID(A2,10,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,16,0),0)

+1),1),

‍

MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",(

IFERROR(IF(FIND(MID(A2,11,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,1,0),0)

+IFERROR(IF(FIND(MID(A2,12,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,2,0),0)

+IFERROR(IF(FIND(MID(A2,13,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,4,0),0)

+IFERROR(IF(FIND(MID(A2,14,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,8,0),0)

+IFERROR(IF(FIND(MID(A2,15,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,16,0),0)

+1),1))

2. Using Salesforce Custom Field

You can also create a new custom formula field in the object where you want to capture the 18-character ID of the Salesforce record, using the CASESAFEID(Id) formula.

‍

You can find more details in this Salesforce help article.   

3. Choosing between the two‍

When it comes time to decide which of the two options is best for you, it’s important to consider the following:

‍

‍

Thank you for reading. We hope this information was helpful. If you have any questions, please don't hesitate to contact us. Request a quote today and let us help you get the most out of your Salesforce investment.

‍