Salesforce Tips: Dealing with 15 vs 18-character Record IDs

Salesforce
Salesforce
About the author
Modelit Content team
Share this article

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.

About the author
Modelit Content team
Share this article
How to integrate Salesforce with Google translate.
How to integrate Salesforce with Google translate.
Re-writing your career path: how to start a job in IT.
Re-writing your career path: how to start a job in IT.
Salesforce Summer ’21 Release is here, we picked up the top 5 new features!
Salesforce Summer ’21 Release is here, we picked up the top 5 new features!
A day to learn, connect and have fun.
A day to learn, connect and have fun.
Welcome to our new website
Welcome to our new website
Introducing a hybrid work model
Introducing a hybrid work model
Building a Digital Experience
Building a Digital Experience
We join the Pledge 1% Movement to donate 1% of staff time to the Community
We join the Pledge 1% Movement to donate 1% of staff time to the Community
Dreamforce is coming!
Dreamforce is coming!
Our Dreamforce 2021 takeaways
Our Dreamforce 2021 takeaways
We are Salesforce Talent Alliance members!
We are Salesforce Talent Alliance members!
What’s it like to work at Modelit?
What’s it like to work at Modelit?
Salesforce’s requirement to Enable Multi-Factor Authentication (MFA)
Salesforce’s requirement to Enable Multi-Factor Authentication (MFA)
Why should you hire a Nearshore team?
Why should you hire a Nearshore team?
The new TrailblazerDX is on its way.
The new TrailblazerDX is on its way.
We've become Salesforce PDO partners!
We've become Salesforce PDO partners!
The Latest and Greatest from TrailblazerDX '22: A Recap of Salesforce's Event
The Latest and Greatest from TrailblazerDX '22: A Recap of Salesforce's Event
How Agile Methodology can help you thrive with Salesforce
How Agile Methodology can help you thrive with Salesforce
The importance of stand-ups: why they're essential for agile teams and clients
The importance of stand-ups: why they're essential for agile teams and clients
How staff augmentation can benefit your organization
How staff augmentation can benefit your organization
How to make the most out of Salesforce
How to make the most out of Salesforce
Dreamforce 2022 is almost here!
Dreamforce 2022 is almost here!
Salesforce Tips: Dealing with 15 vs 18-character Record IDs
Salesforce Tips: Dealing with 15 vs 18-character Record IDs
Your Guide to Dreamforce 2022: How to Survive and Thrive
Your Guide to Dreamforce 2022: How to Survive and Thrive
Why we’re Salesforce certified and you should be too!
Why we’re Salesforce certified and you should be too!
Salesforce Winter ‘23 release: Modelit’s Top 10 Takeaways
Salesforce Winter ‘23 release: Modelit’s Top 10 Takeaways
Why should you complete a Salesforce Health Check?
Why should you complete a Salesforce Health Check?
Dreamforce 2022: On-demand content highlights
Dreamforce 2022: On-demand content highlights
Dreamforce 2022: Modelit’s top takeaways
Dreamforce 2022: Modelit’s top takeaways
Getting to know Salesforce Genie
Getting to know Salesforce Genie
Introducing the Salesforce Associate Certificate
Introducing the Salesforce Associate Certificate
Syncing Contact, Email and Events from your Gmail or Outlook to Salesforce using Einstein Activity Capture
Syncing Contact, Email and Events from your Gmail or Outlook to Salesforce using Einstein Activity Capture
Soft skills in the Salesforce ecosystem
Soft skills in the Salesforce ecosystem
How to maximize your marketing efforts with Salesforce Marketing Cloud
How to maximize your marketing efforts with Salesforce Marketing Cloud
Our year in review: Modelit 2022
Our year in review: Modelit 2022
Modelit launches Bolt, fast-hiring platform for Salesforce talent
Modelit launches Bolt, fast-hiring platform for Salesforce talent
Salesforce Spring ‘23 release: Modelit’s Highlights
Salesforce Spring ‘23 release: Modelit’s Highlights
Economic Uncertainty: How Nearshore Outsourcing Can Help
Economic Uncertainty: How Nearshore Outsourcing Can Help
Notes from the CEO: Is your Salesforce costing more than you think?
Notes from the CEO: Is your Salesforce costing more than you think?
Mastering Staff Augmentation: How Modelit Confronts Communication Challenges
Mastering Staff Augmentation: How Modelit Confronts Communication Challenges
Previous
There are no previous blog posts.
Next
There are no next blog posts.
No comments yet. Be the first!

Leave a reply

Your email address will not be published. Required fields are marked *
Thank you! Your reply has been received!
Oops! Something went wrong while posting the comment.

Get in touch

Message sent successfully.

Thanks so much for contacting us! We received your message and will get back to you within the next 48 hrs.

Your message was not sent.

While we take a look at our server, you can Write us an email.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.