# How can I convert a 15 char Id value into an 18 char Id value?

What is the formula for calculating the additional 3 characters needed to append to a 15 char Id in order to form its equivalent 18 char Id?

I have a situation where I need to compare 15 char Ids (uploaded by users) with their 18 char equivalents (stored in a local database), and it would be most efficient if I could convert the 15 char Ids to 18 chars before comparing (The 18 char Ids are PK fields in my local database, and while I could compare using a `like` query clause, it would be more efficient to convert to 18 chars and perform lookups based on those values).

The base algorithm would be great - even better is an C# implementation.

• On what technology are you trying to calculate the 18 char IDs? ah, I see you! Commented Feb 12, 2014 at 12:40

Explanation of the algorithm: This is based on the algorithm given here. The example below is using a made up salesforce 15 char Id `001A000010khO8J`

1. Separate the 15 char Id into 3 groups of 5 chars. You now have 3 strings (the `triplet` variable below): `001A0`, `00010` and `khO8J`
2. Reverse each string. The three strings are now `0A100`, `01000` and `J8Ohk`
3. In each string, convert all Uppercase chars to 1, all other chars to 0. The three strings are now `01000`, `00000` and `10100`.
4. Look up the corresponding char in the `BinaryIdLookup` based. This gives us a suffix of `IAU`.
5. The 3 chars generated (in order) are appended to the 15 char Id value, giving you an 18 char Id value of `001A000010khO8JIAU`.

I have created an implementation of this in C# and have tested this on a number of real Salesforce Ids and it seems to do the job. Code is in this gist or below:

``````static string Convert15CharTo18CharId(string id)
{
if (string.IsNullOrEmpty(id)) throw new ArgumentNullException("id");
if (id.Length == 18) return id;
if (id.Length != 15) throw
new ArgumentException("Illegal argument length. 15 char string expected.", "id");

var triplet = new List<string> { id.Substring(0, 5),
id.Substring(5, 5),
id.Substring(10, 5) };
var str = new StringBuilder(5);
var suffix = string.Empty;
foreach (var value in triplet)
{
str.Clear();
var reverse = value.Reverse().ToList();
reverse.ForEach(c => str.Append(Char.IsUpper(c) ? "1" : "0"));
suffix += BinaryIdLookup[str.ToString()];
}
return id + suffix;
}

static readonly Dictionary<string, char> BinaryIdLookup = new Dictionary<string, char>
{
{"00000", 'A'}, {"00001", 'B'}, {"00010", 'C'}, {"00011", 'D'}, {"00100", 'E'},
{"00101", 'F'}, {"00110", 'G'}, {"00111", 'H'}, {"01000", 'I'}, {"01001", 'J'},
{"01010", 'K'}, {"01011", 'L'}, {"01100", 'M'}, {"01101", 'N'}, {"01110", 'O'},
{"01111", 'P'}, {"10000", 'Q'}, {"10001", 'R'}, {"10010", 'S'}, {"10011", 'T'},
{"10100", 'U'}, {"10101", 'V'}, {"10110", 'W'}, {"10111", 'X'}, {"11000", 'Y'},
{"11001", 'Z'}, {"11010", '0'}, {"11011", '1'}, {"11100", '2'}, {"11101", '3'},
{"11110", '4'}, {"11111", '5'}
};
``````
• This code is correct, but the example is not. The 13th character in the provided Salesforce ID is an Oh, not a zero. This means the third triplet is actually J8Ohk, which converts to 10100, and thus a 'U'. The actual 18 digit ID is 001A000010khO8JIAU. Commented Jun 21, 2014 at 21:23
• I've corrected the example. Commented Jul 31, 2014 at 9:11
• You can replace `BinaryIdLookup` with the following function: `(str) => {var parsedBinary = Convert.ToInt32(str.ToString(), 2); return (char)(parsedBinary + (parsedBinary < 26 ? 65 : 22));})` Commented Feb 7, 2017 at 18:40

Java version:

``````public class SalesforceIDConverter
{
public static String convertID(String id)
{
if(id.length() == 18) return id;

String suffix = "";
for(int i=0;i<3;i++){

Integer flags = 0;

for(int j=0;j<5;j++){
String c = id.substring(i*5+j,i*5+j+1);

if(c.compareTo("A")  >= 0 && c.compareTo("Z") <= 0){

flags += 1 << j;
}
}

if (flags <= 25) {

suffix += "ABCDEFGHIJKLMNOPQRSTUVWXYZ".substring(flags,flags+1);

}else suffix += "012345".substring(flags-26,flags-26+1);
}

return id+suffix;
}

public static void main(String[] args)
{
String id =  "001M0000009odAH";
String convertedID = convertID(id);
System.out.println("id: " + id + "; converts to: " + convertedID);
}
}
``````

Python version

``````import string
import sys

bin_lookup = {
'00000':'A',
'00001':'B',
'00010':'C',
'00011':'D',
'00100':'E',
'00101':'F',
'00110':'G',
'00111':'H',
'01000':'I',
'01001':'J',
'01010':'K',
'01011':'L',
'01100':'M',
'01101':'N',
'01110':'O',
'01111':'P',
'10000':'Q',
'10001':'R',
'10010':'S',
'10011':'T',
'10100':'U',
'10101':'V',
'10110':'W',
'10111':'X',
'11000':'Y',
'11001':'Z',
'11010':'0',
'11011':'1',
'11100':'2',
'11101':'3',
'11110':'4',
'11111':'5'
}

def sf_replace(incoming_id_char):

if incoming_id_char in string.ascii_uppercase:
return '1'
else:
return '0'

def expand_sf_id(incoming_sf_id):

if len(incoming_sf_id) != 15:
raise ValueError('id string must be exactly 15 characters long')

# split into list of 3 5-character chunks
id_chunks = map("".join, zip(*[iter(incoming_sf_id)]*5))

suffix = ''

for id_chunk in id_chunks:

# replace all capital letters with 1, non cap letters with 0
lookup_components = [sf_replace(id_char) for id_char in id_chunk]

# make it string and reverse it
lookup_chunk = "".join(lookup_components)[::-1]

# get the letter from the lookup table based on the reversed string
bin_replacement = bin_lookup[lookup_chunk]
suffix += bin_replacement

#add the suffix to the origional id
expanded_sf_id = incoming_sf_id + suffix

return expanded_sf_id

if __name__ == '__main__':
print expand_sf_id(sys.argv[1])
``````

In the off chance somebody does the same thing we do and replicate Salesforce data to postgresql, you may find this postgres-function useful. I take no credit for the algorithm, I've simply adapted already listed algorithms to work in plpgsql.

``````CREATE OR REPLACE FUNCTION id15to18(inputid text)
RETURNS text AS
\$BODY\$
DECLARE flags INTEGER DEFAULT 0;
DECLARE suffix TEXT DEFAULT '';
DECLARE chr TEXT DEFAULT NULL;
BEGIN
IF char_length(inputid) != 15 THEN
RETURN inputid;
END IF;
FOR i IN 0 .. 2 LOOP
flags := 0;
FOR j IN 0 .. 4 LOOP
chr := substring(inputid FROM (i)*5+j+1 FOR 1);
IF ( ascii(chr) >= ascii('A') AND ascii(chr) <= ascii('Z') ) THEN
flags := flags + (1 << j);
END IF;
END LOOP;
suffix := suffix || substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ012345' FROM flags+1 FOR 1);
END LOOP;
RETURN inputid || suffix;
END;
\$BODY\$
LANGUAGE plpgsql IMMUTABLE
COST 100;
``````

Somehow there is no Javascript version posted here. Let's remedy that.

``````(function (w) {
w.normalizeId = function (id) {
var i, j, flags, alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",
isUppercase = function(c) {
return c >= "A" && c <= "Z";
};

if (id == null) return id;
id = id.replace(/\W/g, "");
if (id.length != 15) { return id; }

for (i = 0; i < 3; i++) {
flags = 0;
for (j = 0; j < 5; j++) {
if (isUppercase(id.charAt(i * 5 + j))) { flags += 1 << j; }
}
id += alphabet.charAt(flags);
}
return id;
}
})(window);
``````

Here is some information on the algorithm.

And here is a great write up on coding it.

This appears to be taken from here and has (would you believe it!) broken links to a C# example!

• Thanks. I am working on a C# implementation now, will post it when I have it complete and tested. Commented Feb 12, 2014 at 12:45
• Awesome, I think a good google about may dig up the target of the dead links for the C# example, but with those wicked graphic/samples I'd imagine you can knock something together. Would definitely be worth posting it if you do. The15/18 Character ID is a very common query. Glad my links could help. Commented Feb 12, 2014 at 12:47
• I just posted my C# implementation Commented Feb 12, 2014 at 13:32
• Please try to avoid just referencing users to other websites, we aim to maintain information within this site itself. Commented Feb 12, 2014 at 13:58

Published an npm package for this. Took the algorithm from the js answer in this same post :) Thanks Adrian Larson!

https://www.npmjs.com/package/eighteen-digit-salesforce-id

• Cool. I hadn't seen NPM before. Can you explain a little bit about it here for our benefit? Just a brief blurb would be helpful. :) Commented Sep 7, 2016 at 19:22
• NPM is a package manager for the node.js server-side Javascript runtime. If you have node installed, then you already have NPM installed. With NPM, you can install helpful packages with a single command `npm install [package-name]` In this case, `npm install eighteen-digit-salesforce-id` and it will put that package in a `node_modules` folder wherever you run it from. I'd recommend everyone doing server-side JS or using a CommonJS module loader for their front-end code check it out! Commented Sep 7, 2016 at 19:25
• Don't forget you can edit your post. :) Looks cool! Have you used Node much with Salesforce? Commented Sep 7, 2016 at 19:26
• Oh good call, I'll remember that in the future :) I'm actually building my first Salesforce app right now in node. I have multiple components to it, including a connected app (in which I use Node to hit the REST API) and a canvas app (in which I use node/React to bake our pages into SFDC). I haven't hit any hick-ups yet with Node, so I'm excited to push Node a little further into the SFDC space :) Commented Sep 9, 2016 at 14:15

This is not something which I developed. Full credits to the developer: John McTurnan SUB-ROUTINE BY ITSELF (No Demo)

``````//-----------------------------------------------------------------------------------------------------------------------------------
//-----------------------------------------------------------------------------------------------------------------------------------
//-------- SUBROUTINE:  CONVERT SFDC 15 DIGIT KEY TO 18 -----------------------------------------------------------------------------
// Credits:
// John McTurnan:  Conversion from to Qlikview scripting from Javascript ([email protected])
// Ron Hess:  author of the Javascript version (http://boards.developerforce.com/t5/user/viewprofilepage/user-id/198)
//-----------------------------------------------------------------------------------------------------------------------------------
//-----------------------------------------------------------------------------------------------------------------------------------
//-----------------------------------------------------------------------------------------------------------------------------------
Sub SFDC_ID_15_to_18 (v_SFDC_ID15) //

Let v_SFDC_ID18 = v_SFDC_ID15;
Let v_SFDC_ID15 = chr(39) & v_SFDC_ID15 & chr(39);
Let v_Check_digit = chr(39) & 'ABCDEFGHIJKLMNOPQRSTUVWXYZ012345' & chr(39);
Let v_LT = ORD('A');
Let v_GT = ORD('Z');

IF len(v_SFDC_ID15) <> 17 THEN //======  Check if ID conforms to basic length.  Exit & Null if not OK.
Let v_SFDC_ID18 = Null();
Exit Sub;
EndIf;

Set v_i = 1;

For v_i = 1 to 3

Set v_X = 0;
Let v_Block = chr(39) & Mid(\$(v_SFDC_ID15), 5 * (\$(v_i) - 1) + 1, 5) & chr(39);
v_j = 1;

For v_j = 1 TO 5

Let v_C = ORD(Mid(\$(v_Block), \$(v_j), 1));

IF v_LT <= v_C And v_C <= v_GT THEN
Let v_X = \$(v_X) + POW(2,(\$(v_j) - 1));
EndIf;

Next

Let v_temp1 = Mid(\$(v_Check_digit), \$(v_X) + 1, 1);

Let v_SFDC_ID18 = v_SFDC_ID18 & v_temp1;

Next

Let v_SFDC_ID18 = chr(39) & v_SFDC_ID18 & chr(39);

EndSub;
``````
• Assuming this is VBA? Commented Jul 10 at 10:21

I ran across this web page that has an online tool to convert from 15 to 18 characters. I tested it with 1 ID and it was accurate. It's all done in a fairly short amount of JavaScript directly on the `<button>`.

``````<button onclick='javascript:if(document.getElementById("J").value==""){alert("Please enter into the left column a list of IDs on 15 chars");return;}var y=document.getElementById("J").value.split("\r").join("").split("\n");var z=new Array();for(var x=0;x<y.length;x++){if(y[x].length==15){var s="";for(var i=0;i<3; i++){var f=0;for(var j=0;j<5;j++){var c=y[x].charAt(i*5+j);if(c>="A" && c<="Z")f+=1<<j;}s+="ABCDEFGHIJKLMNOPQRSTUVWXYZ012345".charAt(f);}z.push(y[x]+s);}else{alert("Error : "+y[x]+" has not a length of 15 characters"+y[x].length);return;}}document.getElementById("L").value=z.join("\r\n");'>15 => 18</button>
``````

This Excel formula from https://eltoro.secure.force.com/ArticleViewer?id=a07A000000NPRiVIAX

If cell A1 isn't blank, then convert it... You can then copy and paste the value into another cell.

``````=IF( A1<>"",  CONCATENATE(A1,MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",BIN2DEC(TEXT(SUMPRODUCT((CODE(MID(A1,{1,2,3,4,5},1))>64)*(CODE(MID(A1,{1,2,3,4,5},1))<91),{1,10,100,1000,10000}),"00000"))+1,1),MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",BIN2DEC(TEXT(SUMPRODUCT((CODE(MID(A1,{6,7,8,9,10},1))>64)*(CODE(MID(A1,{6,7,8,9,10},1))<91),{1,10,100,1000,10000}),"00000"))+1,1),MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",BIN2DEC(TEXT(SUMPRODUCT((CODE(MID(A1,{11,12,13,14,15},1))>64)*(CODE(MID(A1,{11,12,13,14,15},1))<91),{1,10,100,1000,10000}),"00000"))+1,1)),"")
``````

Java again, bit more old school:

``````public class To18 {
static final String UPPER_CHARS = "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345";

public static void main(String[] args) {
String id = args[0];
if (id.length() != 15) {
System.err.println("Need 15 character string");
return;
}

for (int i = 0; i < 3; i++) {
for (int j = 0; j < 5; j++) {
char x = id.charAt((i*5) + j);
if (Character.isUpperCase(x))
}
}
System.out.println("Converted value: " + id.toUpperCase());
}
}
``````

Here is a PHP version of the conversion function:

``````function convert_sfid(\$sfid15) {
if (strlen(\$sfid15) != 15) {
return \$sfid15;
}

\$chunks = str_split(\$sfid15, 5);
\$extra = '';
foreach (\$chunks as \$chunk) {
\$chars = str_split(\$chunk, 1);
\$bits = '';
foreach (\$chars as \$char) {
\$bits .= (!is_numeric(\$char) && \$char == strtoupper(\$char)) ? '1' : '0';
}
\$map = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ012345';
\$extra .= substr(\$map, base_convert(strrev(\$bits), 2, 10), 1);
}

return \$sfid15 . \$extra;
}
``````

Here is an Objective-C Version:

``````/**
Converts SF 15 digit Id's to SF 18 digit Id's. 15 digit SFID rely's on case sensitivity for uniqueness, 18 digit does not.
@param fifteenDigitId NSString case sensitive version of SFID (15 digit)
@return caseInSensitive NSString 18 digit version of SFID
*/
+ (NSString*)convertToCaseSensitiveSFId:(NSString *)fifteenDigitId {
NSString *eighteenDigitId = nil;

// First check that we aren't trying to convert an already 18 digit Id.
if (fifteenDigitId.length == 18) {
DLog(@"SFID is already 18 digit ID");
eighteenDigitId = fifteenDigitId;
}
else {
NSString *suffix = @"";
for(int i = 0 ; i < 3; i++) {

NSInteger flags = 0;

for(int j = 0; j < 5; j++) {
NSRange range = NSMakeRange(i * 5 + j, 1);
NSString *c = [fifteenDigitId substringWithRange:range];

NSComparisonResult resultA = [c compare:@"A"];
NSComparisonResult resultZ = [c compare:@"Z"];

if ((resultA == NSOrderedDescending || resultA == NSOrderedSame) && (resultZ == NSOrderedAscending || resultZ == NSOrderedSame)) {
flags += 1 << j;
}
}

if (flags <= 25) {
NSRange flagRange = NSMakeRange(flags, 1);
suffix = [NSString stringWithFormat:@"%@%@", suffix, [@"ABCDEFGHIJKLMNOPQRSTUVWXYZ" substringWithRange:flagRange]];
}
else {
NSRange flagRange = NSMakeRange(flags - 26, 1);
suffix = [NSString stringWithFormat:@"%@%@", suffix, [@"012345" substringWithRange:flagRange]];
}
}

eighteenDigitId = [NSString stringWithFormat:@"%@%@", fifteenDigitId, suffix];
}

return eighteenDigitId;
}
``````

more concise (and likely faster) Java version:

``````private static char[] SF_ID_SUFFIX_LOOKUP = "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345".toCharArray();
public static String sfId15To18(final String sfId)
{
if (sfId.length() != 15) {
throw new IllegalArgumentException("Input Salesforce ID must be of length 15.");
}
final StringBuilder sb = new StringBuilder(18).append(sfId15);
for (int i = 0; i < 3; i++) {
for (int j = 0; j < 5; j++) {
if (Character.isUpperCase(sfId15.charAt(5 * i + j))) {
}
}
}
return sb.toString();
}
``````
• My bold "faster" claim was intended relative to the original C# algorithm (which my unit testing showed to be about 3x slower than this one when implemented in Java). This one is likely somewhat faster than the first Java solution posted and very similar to the Java solution posted by Brian Hayes. Commented Mar 24, 2016 at 19:04
• During unit testing I realized I'd make a mistake in the original posting and wasn't reversing the segments of 5 characters for encoding. Now that that's fixed it's essentially identical to Brian Hayes solution - shoulda read further to his solution and saved some time this AM:) Commented Mar 24, 2016 at 22:47

Doing it in Excel using Excel Connector (package of very useful Excel SQL query functions for insert, delete, upsert, etc directly to/from Salesforce.

Hint: =FixID()

Here is Ruby/Rails version.

``````# Params::
# +id+:: 15 character ID to be converted
def get_18_char_id(id)

# split id to strings of three chars each
strings = id.scan(/.{5}/)

# reverse each string in the array
strings.map(&:reverse!)

# convert all 1 to 0
strings = strings.map{|str| str.gsub(/1/,"0")}

# convert all uppercase characters to 1
strings = strings.map{|str| str.gsub(/[A-Z]/,"1")}

# convert all characters to 0 except 1
strings = strings.map{|str| str.gsub(/[^1.]/,"0")}

# replace binary numbers by binary_id_lookup characters based on respective index
strings = strings.map{|str| binary_id_lookup[str.to_i(2)]}

# return the 18 character ID
"#{id}#{strings.join('')}"
end

binary_id_lookup = "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345"
``````

For anyone working on the Grails framework, here's a Groovy solution:

``````def toSalesforceId18(String id) {  //converts salesforce id15 to id18
def valueToCode = { ((it < 26 ? "A" : "0") as char) + it % 26 }
def binaryToCode = { valueToCode(Integer.parseInt(it, 2)) as char }
def tripletToBinary = { it.reverse().replaceAll(/[^A-Z]/, "0").replaceAll(/[^0]/, "1") }
def threeCodes = { it.replaceAll(/(.....)/, { binaryToCode(tripletToBinary(it[0])) }) }
return id?.size() == 15 ? id + threeCodes(id) : id
}
``````

Example:
`toSalesforceId18("001C000000o4Ooi")` --> `"001C000000o4OoiIAE"`

• For the curious, there are over 768,000,000,000,000,000,000,000,000 unique values for id15 (the number is covered in a blog entry of mine). Commented Aug 8, 2014 at 21:19
• In theory, yes but in practical use the amount of unique values is less. The full range is 62^15. First three characters designate which object it is, fourth character tells you which Salesforce instance the object belongs to (or the SF org owning the object rather, I'm not 100% sure, it's reserved at least) so that leaves 62^11 characters So 5.2 x 10^19 per object instead of 7.7 x 10^26. Should still be enough for anyone *famous last words* Commented Nov 21, 2014 at 15:50

Just to add, in Apex there now is the `Id.valueOf` function to do all the work for you:

``````String our18LengthId = (String)Id.valueOf('some15lengthId');
``````

This works because the `valueOf` function can parse 15 length Ids. The resulting Id class then, as always, contains the 18-length Id version of the Id.

Progress 4GL version:

``````def var inputid as char format "x(15)".
def var flags as int init 0.
def var i as int init 0.
def var j as int init 0.
def var chr1 as char.
def var suffix as char.
def var buf1 as int init 0.

UPDATE inputid.
DO:
IF length(inputid) <> 15 THEN
message "ERROR: Length not 15".

do i = 0 to 2:
flags = 0.
do j = 0 to 4:
chr1 = substring(inputid,(i * 5 + j + 1),1).
IF ( asc(chr1) >= asc("A") AND asc(chr1) <= asc("Z") ) THEN
do:
if j = 0 then buf1 = 1.
else if j = 1 then buf1 = 2.
else if j = 2 then buf1 = 4.
else if j = 3 then buf1 = 8.
else if j = 4 then buf1 = 16.

flags = flags + buf1.

end.
END.
suffix = suffix + substring("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",(flags + 1),1).
END.
inputid = inputid + suffix.
END.

message inputid.
``````

Doing it in Excel WITHOUT VBA/code, just formulas.

``````=A1 &

MID(

"ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",

MIN(IF(OR(MID(A1, 5, 1)<65, MID(A1, 5, 1)>90), 0, FIND(MID(A1, 5, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 16 +

MIN(IF(OR(MID(A1, 4, 1)<65, MID(A1, 4, 1)>90), 0, FIND(MID(A1, 4, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 8 +

MIN(IF(OR(MID(A1, 3, 1)<65, MID(A1, 3, 1)>90), 0, FIND(MID(A1, 3, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 4 +

MIN(IF(OR(MID(A1, 2, 1)<65, MID(A1, 2, 1)>90), 0, FIND(MID(A1, 2, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 2 +

MIN(IF(OR(MID(A1, 1, 1)<65, MID(A1, 1, 1)>90), 0, FIND(MID(A1, 1, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 1 + 1,

1) &

MID(

"ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",

MIN(IF(OR(MID(A1, 10, 1)<65, MID(A1, 10, 1)>90), 0, FIND(MID(A1, 10, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 16 +

MIN(IF(OR(MID(A1, 9, 1)<65, MID(A1, 9, 1)>90), 0, FIND(MID(A1, 9, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 8 +

MIN(IF(OR(MID(A1, 8, 1)<65, MID(A1, 8, 1)>90), 0, FIND(MID(A1, 8, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 4 +

MIN(IF(OR(MID(A1, 7, 1)<65, MID(A1, 7, 1)>90), 0, FIND(MID(A1, 7, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 2 +

MIN(IF(OR(MID(A1, 6, 1)<65, MID(A1, 6, 1)>90), 0, FIND(MID(A1, 6, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 1 + 1,

1) &

MID(

"ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",

MIN(IF(OR(MID(A1, 15, 1)<65, MID(A1, 15, 1)>90), 0, FIND(MID(A1, 15, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 16 +

MIN(IF(OR(MID(A1, 14, 1)<65, MID(A1, 14, 1)>90), 0, FIND(MID(A1, 14, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 8 +

MIN(IF(OR(MID(A1, 13, 1)<65, MID(A1, 13, 1)>90), 0, FIND(MID(A1, 13, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 4 +

MIN(IF(OR(MID(A1, 12, 1)<65, MID(A1, 12, 1)>90), 0, FIND(MID(A1, 12, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 2 +

MIN(IF(OR(MID(A1, 11, 1)<65, MID(A1, 11, 1)>90), 0, FIND(MID(A1, 11, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 1 + 1,

1)
``````
• If you're going to include a link, at least include the relevant content. Links tend to break over time, making them brittle and less useful to the community. If your answer can't stand on its own, it will likely be deleted. Commented Nov 23, 2016 at 21:42

I created some code snippets for this task in different programming languages and will add more in the future every time I need the conversion in another language.

Furthermore I added a script, that can be used to extend Google Spreadsheets, as well as a macro written in Basic which can be used in OpenOffice/LibreOffice so a function for the conversion can be applied to Ids in Cells.

You can find these snippets on github: Salesforce 15-digit to 18-digit Id converter

PowerQuery (aka Excel Get and Clean Data)

``````[Account ID]
&
Text.At( "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",
(if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Text.At([Account ID],4)) = true then 1 else 0 ) * 16 +
(if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Text.At([Account ID],3)) = true then 1 else 0 ) * 8 +
(if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Text.At([Account ID],2)) = true then 1 else 0 ) * 4 +
(if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Text.At([Account ID],1)) = true then 1 else 0 ) * 2 +
(if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Text.At([Account ID],0)) = true then 1 else 0 ) * 1)
&
Text.At( "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",
(if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Text.At([Account ID],9)) = true then 1 else 0 ) * 16 +
(if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Text.At([Account ID],8)) = true then 1 else 0 ) * 8 +
(if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Text.At([Account ID],7)) = true then 1 else 0 ) * 4 +
(if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Text.At([Account ID],6)) = true then 1 else 0 ) * 2 +
(if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Text.At([Account ID],5)) = true then 1 else 0 ) * 1)
&
Text.At( "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",
(if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Text.At([Account ID],14)) = true then 1 else 0 ) * 16 +
(if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Text.At([Account ID],13)) = true then 1 else 0 ) * 8 +
(if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Text.At([Account ID],12)) = true then 1 else 0 ) * 4 +
(if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Text.At([Account ID],11)) = true then 1 else 0 ) * 2 +
(if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Text.At([Account ID],10)) = true then 1 else 0 ) * 1)
``````

Here's a version for Snowflake

``````

CREATE OR REPLACE FUNCTION dw.my_schema.f_sfdc_ch15_to_ch18("txt" string)
RETURNS string
LANGUAGE JAVASCRIPT
AS '
if ( txt == undefined || txt == "" || typeof txt == "undefined" || txt == null) {
return ;
} else {
var id15, id18;

if (txt.length == 18) {
return txt;
} else if (txt.length == 15) {
id15 = [txt.trim()];
} else {
return "";
}
for ( var x=0; x < id15.length; x++ ) {
var s = "";
for ( var i=0; i<3; i++) {
var f = 0;
for (var j=0; j<5; j++) {
var c = id15[x].charAt(i*5+j);
if (c>="A" && c<="Z") {
f+=1<<j;
}
}
s += "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345".charAt(f);
}
id18 = id15[x]+s;
}
}
return id18.toString();
';

``````

I know this is an old post, but just in case it is useful to someone, if you want to do ad-hoc conversions of Id's, rather than programatically, then this Chrome extension makes it easy: https://chrome.google.com/webstore/detail/sf-15-to-18/cogllpmaoflgaekieefhmglbpgdgmoeg

FYI - I'm the developer. Please use the feedback form on the app if you'd like to suggest any improvements or additional functionality.

Thanks!

Another Python version :

``````    def sfid18(idToConvert:str):
if not idToConvert:
raise ValueError('No id given.')
if not isinstance(idToConvert, str):
raise TypeError('The given id isn\'t a string')
if len(idToConvert) == 18:
return id
if len(idToConvert) != 15:
raise ValueError('The given id isn\'t 15 characters long.')
for x in range(0, 15, 5):
c = idToConvert[x:x+5]
d = ''
for y in (c[::-1]):
if y.isupper():
d += '1'
else:
d += '0'
idToConvert += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ12345'[int(d, 2)]
return idToConvert
``````

Another Excel formula version:

``````   =   A1
& MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ12345",
BIN2DEC(TEXTJOIN("",1,IF(ISNUMBER(MID(LEFT(A1,5),{​5,4,3,2,1}​
,1)*1),0,IF(EXACT(MID(LEFT(A1,5),{​5,4,3,2,1}​,1),UPPER(MID(LEFT(A1,5),{​
5,4,3,2,1}​,1))),1,0))))+1,1)
&
MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ12345",
BIN2DEC(TEXTJOIN("",1,IF(ISNUMBER(MID(MID(A1,6,5),{​5,4,3,2,1}​
,1)*1),0,IF(EXACT(MID(MID(A1,6,5),{​5,4,3,2,1}​,1),UPPER(MID(MID(A1,6,5),{​
5,4,3,2,1}​,1))),1,0))))+1,1)
&
MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ12345",
BIN2DEC(TEXTJOIN("",1,IF(ISNUMBER(MID(RIGHT(A1,5),{​5,4,3,2,1}​
,1)*1),0,IF(EXACT(MID(RIGHT(A1,5),{​5,4,3,2,1}​,1),UPPER(MID(RIGHT(A1,5),{​
5,4,3,2,1}​,1))),1,0))))+1,1)

``````

A Power Query M function:

``````(id15 as text) as text =>
let
uppers = "ABCDEFGHIJKLMNOPQRSTUVWXYZ",
case_codes = "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",
getValAtIndex = (i, j) =>
if Text.Contains(uppers, Text.At(id15, i * 5 + j)) then Number.Power(2, j) else 0,
getCaseCode = (i) =>
Text.At(case_codes, List.Sum(List.Transform({0 .. 4}, each getValAtIndex(i, _)))),
id18_codes = List.Transform({0 .. 2}, each getCaseCode(_))
in
id15 & Text.Combine(id18_codes)
``````

And another Python version:

``````def sf15to18(x):
uppers = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
case_codes = "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345"
for i in range(3):
b = 0
for j in range(5):
if x[i * 5 + j] in uppers:
b += pow(2, j)
x += case_codes[b]
return x
``````