+ Start a Discussion
ErikMittmeyerErikMittmeyer 

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 DeveloperLeadClic 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 LearnerApex Learner

Thanks a lot ..

Solved my issue

Really great work /...

AReeseAReese

Thank 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_1978rk_1978

WOWWWWW !!!!

Perfect.. 

 

Great ErikMittmeyer

owensciowensci

That's Genius! THANK YOU!!!

jaw999jaw999

This is an excellent post.

Vagish *Vagish *

This post really cool..

 

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

 

cheers!

kotoponuskotoponus

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 ThomasRyan 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 BeharEphraim Behar
Thank You for the excel version!
Bhavesh Jha 33Bhavesh Jha 33
Thanks @eric 
James Gross 7James Gross 7
Here'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.
deepblueseadeepbluesea
Thanks 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 8Nick Liechty 8
Brilliant formula! Worked like a charm!
Gonzalo ParraGonzalo Parra
Thank 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...
deepblueseadeepbluesea
Good Job, Gonzalo! My original post has come a long way :) /Erik
Andrew Kuharich 01Andrew Kuharich 01
The 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 21Hoa Nguyen 21
thank you very much for the formulas and vba codes