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

Question

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.

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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;

Answer 5

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

Answer 6

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!

Answer 7

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

Answer 8

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;

Answer 9

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>

Answer 10

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

Answer 11

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

Answer 12

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

Answer 13

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

Answer 14

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

Answer 15

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

Answer 16

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"

Answer 17

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"

Answer 18

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.

Answer 19

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.

Answer 20

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)

Answer 21

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

Answer 22

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

Answer 23

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

Answer 24

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!

Answer 25

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

Answer 26

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)

Answer 27

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