 ShowAll Questionssorted byDate Posted ErikMittmeyer

# 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 Learner

Thanks a lot ..

Solved my issue

Really great work /... AReese

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_1978

WOWWWWW !!!!

Perfect..

Great ErikMittmeyer owensci

That's Genius! THANK YOU!!! jaw999

This 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 Behar
Thank You for the excel version! Bhavesh Jha 33
Thanks @eric James 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. deepbluesea
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 8
Brilliant formula! Worked like a charm! Gonzalo 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... deepbluesea
Good Job, Gonzalo! My original post has come a long way :) /Erik Andrew Kuharich 01
The first formula posted by ErikMittmeyer, Awesome! Works great.
We were given an Excel formula from SF that, we were told, would be absolutly unique.
Does this formula do that?
Thanks! Hoa Nguyen 21
thank you very much for the formulas and vba codes Adam Cadam
In Salesforce, in a Formula Field, you can simply use:

`CASESAFEID(Id)`

And it's all done for you! mmotley NILESH RAJ 20 Adam Cadman 4 weno bak