37

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! – Simon Lawrence Feb 12 '14 at 12:40

23 Answers 23

42

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. – Ben Gottlieb Jun 21 '14 at 21:23
  • I've corrected the example. – Alex Tennant Jul 31 '14 at 9:11
  • 1
    You can replace BinaryIdLookup with the following function: (str) => {var parsedBinary = Convert.ToInt32(str.ToString(), 2); return (char)(parsedBinary + (parsedBinary < 26 ? 65 : 22));}) – Arithmomaniac Feb 7 '17 at 18:40
6

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);
    }
}
5

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])
3

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. – Yaakov Ellis Feb 12 '14 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. – Simon Lawrence Feb 12 '14 at 12:47
  • 1
    I just posted my C# implementation – Yaakov Ellis Feb 12 '14 at 13:32
  • Please try to avoid just referencing users to other websites, we aim to maintain information within this site itself. – Samuel De Rycke Feb 12 '14 at 13:58
3

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 (jmcturnan@gmail.com)
// 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;
2

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;
2

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>
2

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);
2

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

  • 1
    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. :) – Adrian Larson Sep 7 '16 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! – thataustin Sep 7 '16 at 19:25
  • Don't forget you can edit your post. :) Looks cool! Have you used Node much with Salesforce? – Adrian Larson Sep 7 '16 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 :) – thataustin Sep 9 '16 at 14:15
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++) {
            int mask = 0;
            for (int j = 0; j < 5; j++) {
                char x = id.charAt((i*5) + j);
                if (Character.isUpperCase(x))
                    mask += Math.pow(2, j);
            }
            id += UPPER_CHARS.charAt(mask);
        }
        System.out.println("Converted value: " + id.toUpperCase());
    }
}
1

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;
}
1

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;
}
1

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++) {
        int mask = 0x0;
        for (int j = 0; j < 5; j++) {
            if (Character.isUpperCase(sfId15.charAt(5 * i + j))) {
                mask |= (0x1 << j);
            }
        }
        sb.append(SF_ID_MASK_TO_SUFFIX[mask]);
    }
    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. – James Courtney Mar 24 '16 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:) – James Courtney Mar 24 '16 at 22:47
1

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"
1

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). – Dem Pilafian Aug 8 '14 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* – Martin Peters Nov 21 '14 at 15:50
1

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)),"")
0

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.
0

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()

0

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. – Adrian Larson Nov 23 '16 at 21:42
0

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

0

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)

Thanks to https://sites.google.com/site/ezrakenigsberg/blog/greatnewfixforthe15-characteridproblem

0

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.

-1

This answer is much better, instead of hacking out the salesforce algorithm ourselves. https://salesforce.stackexchange.com/a/25576/40627

  • 1
    please add some more details to your answer, so visitors know, what it is about. – Basti Nov 9 '17 at 10:54
  • This is only helpful within Salesforce. If you are working with SF Id's outside of SF, then you need to do it yourself – cyberspy Mar 17 at 13:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.