You need to sign in to do that

Don't have an account?

# Formula Field: calculating the 18-digit ID from the 15-digit ID

Simply put the following formula into a formula field of type "Text":

Id

& MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",(

IF(FIND(MID(Id,1,1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,1,0)

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

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

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

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

)+1,1)

& MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",(

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

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

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

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

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

)+1,1)

& MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",(

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

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

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

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

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

)+1,1)

This is an optimized version of the code example "BPS: 18 digit external ID within the UI" you can find on the Online Help, but unlike the example provided there, it is small enough to fit into a formula field. Also, this code doesn't contain the error found in the online help example, where the second character byte equivalent of the first 5-character chunk was "4" instead of "2". I'd be more than happy if someone could come up with an even shorter version of this formula...Still, I hope you can use this.

Cheers,

Erik Mittmeyer

LeadClic Developer

Hi there... this is an extremely good answer... adding a formula field "id18" on most important objects makes a BIG difference when having to face "outside" field matching (for example in access) or "inside" id matching (something we had to face with partner portal integration).

Thank you very much for the Formula Field!Apex LearnerThanks a lot ..

Solved my issue

Really great work /...

AReeseThank you! That was the easiest one that I've seen yet. It will help tremendously to simply run a report to include either the 15 digit Id, 18 digit Id or both! I have put it on the most commonly used Objects which prevents me from having to do an export from DL each time I want the 18 digit Id!

rk_1978

WOWWWWW !!!!

Perfect..

Great ErikMittmeyer

owensciThat's Genius! THANK YOU!!!

jaw999This is an excellent post.

Vagish *

This post really cool..

Now a native method is avaliable: CASESAFEID(id), it returns 18 char id.

cheers!

kotoponus

I second the use of CASESAFEID function. I definitly have benefited from the original post when the native function is not around, but this new function also checks to return the null value (or "-" in reports) if an empty value is passed.

This may seems like an odd feature to have as every object must have an id value as a unique identifier. But this is useful when you have a custom field which refers to another object and the value for the custom field is optional. Under that situation, sometimes you want to see the Ids for the value that the custom field is referring to (e.g. a channel partner field on Opportunity referencing Account), this would be important as the original formula will return "AAA" when the custom field is null.

If you still want to use the original formula posted here, you may want to place an IF statement to return null when the id value is blank. I think it is better to get a native function to the dirty job as they will cover any future eventualites if you do not have a specific reason to use the original one.

Ryan Thomas

For those who want to use the formula in an Excel spreadsheet, I modified the syntax to make it work in Excel:

NOTE:In my formula, I didn't use Id. I used the cell where the Id is stored. In this example, that is A2.=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))

Ephraim BeharThank You for the excel version! Bhavesh Jha 33Thanks @eric James Gross 7Here's a simpler more compact formula for Excel along similar lines as above:

=A2&MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",1

+IF(CODE(MID(A2,1,1))>64,1,0)*IF(CODE(MID(A2,1,1))<91,1,0)*1

+IF(CODE(MID(A2,2,1))>64,1,0)*IF(CODE(MID(A2,2,1))<91,1,0)*2

+IF(CODE(MID(A2,3,1))>64,1,0)*IF(CODE(MID(A2,3,1))<91,1,0)*4

+IF(CODE(MID(A2,4,1))>64,1,0)*IF(CODE(MID(A2,4,1))<91,1,0)*8

+IF(CODE(MID(A2,5,1))>64,1,0)*IF(CODE(MID(A2,5,1))<91,1,0)*16,1)

&MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",1

+IF(CODE(MID(A2,6,1))>64,1,0)*IF(CODE(MID(A2,6,1))<91,1,0)*1

+IF(CODE(MID(A2,7,1))>64,1,0)*IF(CODE(MID(A2,7,1))<91,1,0)*2

+IF(CODE(MID(A2,8,1))>64,1,0)*IF(CODE(MID(A2,8,1))<91,1,0)*4

+IF(CODE(MID(A2,9,1))>64,1,0)*IF(CODE(MID(A2,9,1))<91,1,0)*8

+IF(CODE(MID(A2,10,1))>64,1,0)*IF(CODE(MID(A2,10,1))<91,1,0)*16,1)

&MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",1

+IF(CODE(MID(A2,11,1))>64,1,0)*IF(CODE(MID(A2,11,1))<91,1,0)*1

+IF(CODE(MID(A2,12,1))>64,1,0)*IF(CODE(MID(A2,12,1))<91,1,0)*2

+IF(CODE(MID(A2,13,1))>64,1,0)*IF(CODE(MID(A2,13,1))<91,1,0)*4

+IF(CODE(MID(A2,14,1))>64,1,0)*IF(CODE(MID(A2,14,1))<91,1,0)*8

+IF(CODE(MID(A2,15,1))>64,1,0)*IF(CODE(MID(A2,15,1))<91,1,0)*16,1)

You'll need to replace "A2" with the reference of the cell containing the 15-character ID in your spreadsheet, and strip the newlines out of the text above. Instead of searching a string of capital letters, this formula checks to see if the character's character code (ASCII code) is within the range of capital letters, from 65 - 90, avoiding the need for internal error checking.

deepblueseaThanks for sharing!!! If you are going to do this in Excel, there is an even better approach via VBA. The code was provided for 32-bit Excel by Scot Stoney and can be found in the exceptional and still very relevant "sForce Connector" by Ron Hess (I still use it, although it forces me to keep a copy of Excel 2003 on my machine). If you put this into a new VBA module, then you can use it inline, e.g. "=FixID([cell])", a HUGE time saver.

Function FixID(InID As String) As String

If Len(InID) = 18 Then

FixID = InID

Exit Function

End If

Dim InChars As String, InI As Integer, InUpper As String

Dim InCnt As Integer

InChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345"

InUpper = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

InCnt = 0

For InI = 15 To 1 Step -1

InCnt = 2 * InCnt + Sgn(InStr(1, InUpper, Mid(InID, InI, 1), vbBinaryCompare))

If InI Mod 5 = 1 Then

FixID = Mid(InChars, InCnt + 1, 1) + FixID

InCnt = 0

End If

Next InI

FixID = InID + FixID

End Function

Nick Liechty 8Brilliant formula! Worked like a charm! Gonzalo ParraThank you! Very useful answers here!

I needed this in Powershell so I ported deepbluesea VBA, here it is in case anyone needs it:

Function Generate_18CharSFID($SalesForceID) {

$InChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345"

$InUpper = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray() | %{[int][char]$_}

$SalesForceID_Rev = $SalesForceID | %{ -join $_[$_.Length..0] }

$SalesForceID_RevArray = $SalesForceID_Rev.ToCharArray() | %{[int][char]$_}

$Checksum = ""

$InCnt = 0

$pos = 15

foreach ($Char in $SalesForceID_RevArray) {

$InCnt = (2 * $InCnt) + $InUpper.Contains($Char)

if ($pos % 5 -eq 1) {

$Checksum = $InChars.Substring($InCnt,1) + $Checksum

$InCnt = 0

}

$pos = $pos - 1

}

return $SalesForceID + $Checksum

}

I'm sure this code can be optimized but it works...

deepblueseaGood Job, Gonzalo! My original post has come a long way :) /Erik Andrew Kuharich 01The first formula posted by ErikMittmeyer, Awesome! Works great. Question about this: Does this create a UNIQUE id accoss SF (not just HCM)?

We were given an Excel formula from SF that, we were told, would be absolutly unique.

Does this formula do that?

Thanks!

Hoa Nguyen 21thank you very much for the formulas and vba codes Adam CadamIn Salesforce, in a Formula Field, you can simply use:

And it's all done for you!

mmotleyAdam Cadam wins. NILESH RAJ 20You can use CASESAFEID(Id) as a formula field to convert the IDs or if you need to convert bulk Ids for data load operations use can use a simple tool: Salesforce 15-to-18 digit ID converter (https://sfdcwisdom.com/salesforce-15-to-18-digit/). Adam Cadman 4If you want to do conversions manually, there's an excellent extension for Chrome that does just this: https://chrome.google.com/webstore/detail/sf-15-to-18/cogllpmaoflgaekieefhmglbpgdgmoeg?h1=en

weno bakBetter than average information, gainful and sensational framework, as offer well finished with shrewd contemplations and thoughts, clusters of exceptional information and inspiration, both of which I require, by virtue of offering such obliging information here. If you like strategy type video game then visit the link and know interesting facts about “ Full list cheats code of GTA SA PC” and click here for “Latest PlayStation GTA SA Cheats Code”..