markus.codes

When working with Salesforce you’ll come across your record’s IDs at some point in time. When having a closer look, you’ll notice that there is a 15- as well as an 18-character version for the same record. Why is that and how can you convert them? It is easy and can be done in different Salesforce Clouds, Excel, your own progams and even online directly within this blogpost! Check it out!

Differences between the 15 and 18-character IDs

It might seem weird to you that two different IDs identify the same record. But if you take a closer look, you’ll see that the first 15 characters are the same for these IDs. So what are the additional characters for?

They’ve been introduced to ensure the ID is case-insensitive and can also be used in systems and tools where case sensitivity isn’t given. So if you mix up lowecase and uppercase characters in the short version of an ID you’ll get in trouble as you’ll possibly find youself looking at a different record or an error message.

When to use which ID?

If you don’t know whether a system or tool you are working with is case-sensitive or not, you can always go with the longer version of an ID. The standard API also retuns the 18-character ID at all times. In case you use Marketing Cloud in combination with the CRM, you might already know that it also uses the 18-character ID for your subscribers.

However, in some places you can only get the 15-character version. For example when exporting a report or displaying an ID somewhere. That might also be the case if you get the information from a different tool that has been integrated into the IT landscape with the short IDs only.

So how can you convert an ID?

Within the Salesforce Platform there are functions to get the 18-character representation when given the short version. This can be done using a formula field and the CASESAFEID-function in the CRM or using AMPscript and the LongSFID-function in Marketing Cloud:

%%=LongSFID('0036000000QKv5T')=%%

If you need a conversion elsewhere, you can achieve that using a small code snippet in the language of your choice. In my “sf15to18” GitHub-repository I provide you with code snippets in a couple of programming languages including JavaScript and Python. Furthermore I created scripts that can be used in Excel or OpenOffice/LibreOffice as well as in Google Sheets. Those scripts come in handy if you receive an exported report or data from another system where you need to convert the IDs for further processing.

Below, you’ll find an online converter as well as the necessary steps to add the function to an Excel spreadsheet!

Convert an ID online

If you just want to convert a single ID manually, you can use the form below and do the conversion directly within your browser:

You want to find out how it works?! Find out below the excel section of this post or review the code on GitHub.

Microsoft Excel version

In order to use the conversion function in Microsoft Excel, just follow these six simple steps:

Adding the sf15to18-function to Excel

1. Open or create a spreadsheet in Excel
2. Go to Tools > Macro > Visual Basic Editor
3. Right click on This Workbook and select Insert > Module
4. Copy and paste the contents of sf15to18.bas (see below) to the editor
5. Save the macro
6. Use it in your spreadsheet. If you want to convert the value of Cell B1, you need to use the following formula for example: =sf15to18(B1)

sf15to18.bas

Function sf15to18(id As String) As String
  If Len(id) = 15 Then
    For i = 0 To 2
      Dim f
      f = 0

      For j = 0 To 4
        Dim c
        c = Asc(Mid(id, (i * 5 + j + 1), 1))
  
        If c >= 65 And c <= 90 Then
          f = f + (1 * (2 ^ j))
        End If
      Next j
      id = id + Mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345", (f + 1), 1)
    Next i
    sf15to18 = id
  Else
    If Len(id) = 18 Then
      sf15to18 = id
    Else
      sf15to18 = ""
    End If
  End If
End Function

How it works

Getting the case insensitive 18-character ID is an easy process which basically works like this:

1. The 15-digit ID is split into three 5-character segments.
2. Each of these segments is then reversed.
3. Every uppercase letter is replaced with a 1, every other character with a 0.
4. The three suffix-digits for the Id can then be looked up in a table, segment by segment (first segment gives the first digit, etc.). The table below shows an excerpt of such a table, which can be easily completed.

Lookup table

Related resources

• sf15to18 repository on GitHub
• LongSFID AMPscript function
• Convert a 15-character Id to an 18-character Id using a formula field in Salesforce