Salesforce 15 to 18 Digit ID: CASESAFEID and Excel Formula

Use the 18-digit ID any time a Salesforce ID leaves Salesforce: in a CSV you edit, an Excel lookup, or another system. The 15-digit ID is case-sensitive, so 001000000000abc and 001000000000ABC are two different records. Excel's VLOOKUP, XLOOKUP and MATCH ignore case, so they treat those two IDs as the same value and can return the wrong record, which then gets loaded back into Salesforce.

The 18-digit ID is the same 15 characters plus a 3-character suffix that encodes which letters are uppercase. That keeps every ID unique even when case is ignored: the two IDs above become 001000000000abcAAA and 001000000000ABCAA2. (These are example IDs.)

To show the 18-digit ID in reports and exports, create a formula field on the object (return type Text) with:

CASESAFEID(Id)

Already have a report export full of 15-digit IDs? Put this in Excel next to the first ID (in cell A2) and fill down:

=A2&MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",1+SUMPRODUCT((CODE(MID(A2,{1,2,3,4,5},1))>=65)*(CODE(MID(A2,{1,2,3,4,5},1))<=90)*{1,2,4,8,16}),1)&MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",1+SUMPRODUCT((CODE(MID(A2,{6,7,8,9,10},1))>=65)*(CODE(MID(A2,{6,7,8,9,10},1))<=90)*{1,2,4,8,16}),1)&MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",1+SUMPRODUCT((CODE(MID(A2,{11,12,13,14,15},1))>=65)*(CODE(MID(A2,{11,12,13,14,15},1))<=90)*{1,2,4,8,16}),1)

1. Convert 15-character IDs to 18 characters

Paste your 15-character IDs below, one per line, then copy the 18-character versions back into your spreadsheet. The converter adds the same 3-character suffix as CASESAFEID, so you get the IDs Salesforce would give you. Lines that aren't 15 or 18 letters and numbers are flagged.

For example, 001xx000003DHur becomes 001xx000003DHurAAG, which is the example in Salesforce's CASESAFEID help page.

2. What are Salesforce record IDs?

Record IDs are the unique values for each record in Salesforce. Every Contact, Account, and every other object has a unique ID assigned to it automatically by Salesforce. Each record has two versions of its ID: a 15-digit one and an 18-digit one. IDs are letters and numbers, so "15-character" is more precise than "15-digit", but both terms mean the same thing.

3. The difference between 15 and 18 digit IDs

The 15-digit ID is case-sensitive. Salesforce shows it in Classic URLs and report exports. Because it is case-sensitive, Contact A can have the ID 003000000000abc and Contact B can have the ID 003000000000ABC, and Salesforce treats them as two different records.

The 18-digit ID is the 15-digit ID with three extra characters on the end. Those characters record which letters in the first 15 are uppercase, so 003000000000abc becomes 003000000000abcAAA and 003000000000ABC becomes 003000000000ABCAA2. Even if a tool ignores case, the two stay different.

4. Which ID to use when loading data

Use the 18-digit ID in any file you edit before loading into Salesforce:

• A VLOOKUP in Excel ignores case, so it can match the wrong 15-digit ID and give you incorrect data.
• Other external systems, such as Microsoft Access, also ignore case when deciding whether a value is unique.

Salesforce accepts both 15 and 18-digit IDs on import, so the risk is in the spreadsheet. Wrong or mismatched IDs are behind several of the common Salesforce data loading errors. Excel causes a similar problem with dates. See Salesforce date formats for CSV imports.

5. How to get the 18-digit ID

One way to view the 18-digit ID for an individual record is to look in the URL when you are viewing the record in Lightning, for example:

If you want to view 18-digit IDs in a report, create a formula field on each object where you need the ID. Salesforce has a built-in formula for this:

CASESAFEID(Id)

The 18-digit ID also shows up in:

• Lightning record URLs
• Data Loader and API exports
• Dataimporter exports

6. Which ID a report export shows

If you export data from a Salesforce report, the 15-digit ID is shown and exported to CSV. Unless you have created a case-safe ID field and added it to your report, the standard Id field will be in the 15-digit format. This means you need to be careful when you export data from a report and then import from that file. If you already have a report export, the Excel formula at the top of this post converts the IDs. If you export records using Dataimporter, the Id column is the 18-digit case-safe value, so you can map it straight back on a CSV or Excel load without a formula field on every object.

7. Summary

While Salesforce Data Loader and other tools such as Dataimporter can recognise the 15-digit ID, it is always better to use the 18-digit ID. It makes sure any data manipulation you do in Excel works as expected, and that your data import is clean and accurate. Using 18-digit IDs and External IDs are both on our Salesforce data migration checklist.

Frequently asked questions

How do I convert a 15-digit Salesforce ID to 18 digits?

In Salesforce, create a Text formula field with CASESAFEID(Id) and add it to your report. For IDs you've already exported, paste them into the converter above or use the Excel formula at the top of the post. Both add the same 3-character suffix Salesforce would.

Can I use 15-digit IDs in Data Loader or the API?

Yes. Salesforce accepts either length when you update or look up records, and the API returns 18-digit IDs. The problem is matching or deduplicating 15-digit IDs in tools that ignore case, like Excel, before the load.

Why do Salesforce reports show 15-digit IDs?

Reports return the 15-character ID, while the API, Data Loader and Lightning URLs use 18 characters. That's why a report export and a Data Loader export of the same records won't match in a VLOOKUP until you convert one side.

Is the 18-digit ID the same record as the 15-digit ID?

Yes. The first 15 characters are identical. The last three are a check suffix based on capitalisation, so you can always go from 15 to 18. Going from 18 to 15 is just dropping the last three characters.

Do record IDs stay the same when I migrate to another Salesforce org?

No. Every org creates new IDs on insert, so IDs from the old org won't work as lookups in the new one. Store the old ID in an External ID field and use it to link related records. See migrating data from one Salesforce org to another.