function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
astroastro 

Convert 15 digit ID to 18

Hey guys,

 

needed a way to convert from a 15 digit ID to an 18 digit one.

Found the javascript version for just such an action here: http://community.salesforce.com/sforce/board/message?board.id=general_development&message.id=13148

 

tried to make my own apex version but the last digit keeps coming out wrong.  Any Ideas?

 

Thank's

 

 

 
String id = '001Q0000002NXad'; //a sample id
 
String suffix = '';

for(integer i=0;i<3;i++){ 

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

		if(c >= 'A' && c <='Z'){ 

			flags += 1 << j; 
		} 
	} 
	
	if (flags <= 25) { 

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

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

System.debug('15-DIGIT:'+Id);
System.debug('18-DIGIT:'+Id + suffix);
 
 
 
Message Edited by astro on  05-01-2009 10:04 PM
Message Edited by astro on  05-01-2009 10:10 PM


ShikibuShikibu
Did you have a look in salesforce help? It doesn't provide apex code, but it does have an algorithm in English. Search for "18 character ID", or click here.
 
There's also a python module in an open source project called pyax (written by my colleague), which you may find helpful. You can find pyax.sobject.util.id_15_to_18 at launchpad. 
 
If you write working apex code, please do publish it back here; I'm sure many others would benefit. 


ShikibuShikibu
astro, your code is in a code box, but it's all mangled anyway. I was having that problem, using Safari 3 on OSX, and found that if I switch to Firefox, it works.

I wonder if Salesforce has changed something recently that is causing a bad interaction with Safari, because I don't recall this happening last week (tho I was using Safari 4 last week). 


BritishBoyinDCBritishBoyinDC
Here's the VBA macro from the connector that should help:
 
 
Function FixID(InID As String) As StringDim InChars As String, InI As Integer, InUpper As StringDim InCnt As IntegerInChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345"InUpper = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"InCnt = 0For 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 + FixIDEnd Function
 
 
 


BritishBoyinDCBritishBoyinDC
Let's try that in Firefox:
 
 
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
 
 


astroastro
thank's guys for all your responses, and code snippets, I will try and fix my code based on what snippets you gave me.
 
And I tried looking for the algorithm in the salesforce released materials but couldn't find it, I'll try again.
*just noticed the link you put up shikibu thank's for that. 
 
It's annoying me because it's just the last digit that is off lol
 
I'll definitely post it up on the code share when I get it going.
 
Thank's again 
 
*also i re-posted the code again in ffox, sorry about it not being readable before. 
 
 
Message Edited by astro on  05-01-2009 10:06 PM
Message Edited by astro on  05-01-2009 10:15 PM


astroastro
not sure of any way to do something such as this in salesforce which is why I don't think I can find it.
 
Salesforce treats lowercase and uppercase char's equally.  meaning they hold the same value, weather htat's ascii or not, i'm not sure.  Strings and Integer's do not mix in this world 
 
 
I can't seem to find a way obtain the ascii value for a string.
 
Am I missing something here or can this not be done with apex?
 
my previous code was failing on the if( c >='A' && c <='Z') part because uppercase or lower it was always true
 
....... 


ShikibuShikibu
Have you tried using touppercase or tolowercase?


SuperfellSuperfell
Can you backup a step and explain why you need this ?


astroastro
shikibu: yes I have tried toUppercase toLowercase no difference sadly
 
sure I'll back up and explain the use case:
doing an integration from one system to another and one of the values being passed from the outside system are SFDC ID's.  They iwll be sending both 15 and 18 digit id's so I need a way of converting them all to 18 or 15 and having my logic to the rest of the work based on that standard.
 
Hope that helps.
 
also tried Integer.valueOf(''); but that only works for strings that contain numbers. 


ShikibuShikibu
In that case, you don't need to do anything.
 
 
ID id18 = '50020000003Oh54AAC';
ID id15 = '50020000003Oh54';

system.debug( id18 == id15 );

20090502235841.353:AnonymousBlock: line 4, column 1: true

 
 
 
 
I believe that the extra three characters are check digits, a kind of error correction for systems (some Microsoft product?) that is case-insensitive, while Salesforce object IDs require upper and lower case in the first 15 characters.
 
In any case, as long as you declare the type as ID, rather than String, ids that refer to the same object will compare equal, whether they are 15 or 18 characters. 
 
If this is inaccurate, I'm sure Simon will be able to provide a better answer. 


astroastro
ahh excellent.  There was a particular reason why I moved all my ID's to Strings but I forget, I'll rework my code to fit with ID's and work around it since it's better than what I'm current ly doing, (checking the  string size and cutting it down to only 15 if it's 18 ..which actually may not work all the time if the cases are off.
 
Thank's for the insight and quick response I truly appreciate it! 


ShikibuShikibu
Make sure that whatever system is providing you with 18 characters will not fail if you give it back 15. (I think that so long as your external systems don't mess with the case of the IDs, they should be fine).


astroastro
Yes very good point, I have told them to be aware of this.
 
argh integrations :) 


SD_ChargersSD_Chargers
Hi Astro,
 
 Did you get find a solution to how to convert 15 digit IDs to 18?
 
SD


TaoDuhTaoDuh
I almost didn't believe it myself but this works.


 


    private String idsTo18(String initialVal) {
        if (initialVal.length() != 15) return initialVal;
        ID convertToLong = initialVal;
        String longId = convertToLong;
        return longId;
    }


 


My need for this function is when you build a SOQL query string for execution, the string is case insensitive.  If I have 15-character IDs I can get too many hits.


CAfromCACAfromCA
Standing on the shoulders of giants, I wrote this for the fun of it:
 
 
public static String ID15to18(String inID) {
  if (inID.length() == 18) { return inID; }
  else if (inID.length() != 15) { return ''; }
  else {
    String suffix = '';
    for (Integer i = 0; i < 3; i++) {
      Integer flags = 0;
      for (Integer j = 0; j < 5; j++) {
        String c = inID.substring(i*5+j, i*5+j+1);
        if ( ( c.compareTo('A') >= 0 ) && ( c.compareTo('Z') <= 0 ) ) {
          flags += 1 << j;
        }
      }
      suffix = suffix + 'ABCDEFGHIJKLMNOPQRSTUVWXYZ012345'.substring(flags, flags+1);
    }
    return inID + suffix;
  }
}
 I make no guarantees, but it seems to work in my quick-and-dirty tests.
 


ErikMittmeyerErikMittmeyer
Did you ever consider creating an according formula field instead? You can easily add this to reports and export case-insensitive IDs to any app that needs them (e.g. Excel or Access). Look up http://community.salesforce.com/t5/Apex-Code-Development/Formula-Field-calculating-the-18-digit-ID-from-the-15-digit-ID/td-p/189848 for details.

Hope this helps,
Erik


d19engeld19engel
Would it help if you could export the IDs into a Google Spreadsheet and convert them there?
 
Here is a help article describing how to convert 15 to 18 digit salesforce ids in google spreadsheets. It's designed to be very easy so any user can do it.


Nisse Knudsen.ax1026Nisse Knudsen.ax1026
Hehe, I like that solution! Works fine for me too! ;)


Ankur_ShuklaAnkur_Shukla
Hi - You can use this web page to do a quick a convert for a single record.
Thanks,
Ankur

http://ankurshukla-developer-edition.na12.force.com/CovertId/ConvertSalesforceID


colliecollie
If I download my data from Sales Force and make changes. How do I upload using Apex data loader to upload the revised data?
I need a quick and easy solution. thanks


Starz26Starz26
Why not just assing the ID to a string. Salesforce does the conversion for you:
 
String a = Account.ID;
 
Bam, a = the 18 chr id


Starz26Starz26
collie wrote:
If I download my data from Sales Force and make changes. How do I upload using Apex data loader to upload the revised data?
I need a quick and easy solution. thanks
Um, Just upsert the data back using dataloader


jwalshjwalsh
Pure java version, in case anyone ever needed it (props @astro and @AndyH47):
 
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);
    }
}
 


CLRGCLRG
In case anyone needs this inside sql server, which I did, here's the function I wrote:
 
USE [urdb]
GO
/****** Object:  UserDefinedFunction [dbo].[FX_convertIdTo18Digit]    Script Date: 01/07/2013 16:16:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER function [dbo].[FX_convertIdTo18Digit](@InID nvarchar(18)) returns NCHAR(18) 
AS
BEGIN
-- DECLARE @InID nchar(15)='xxxxxxxxxxxxxxx'
/*
to test run this script which should return 0 records
SELECT top 5000 ID,DBO.FX_convertIdTo18Digit(LEFT(id,15)) myid into #tmp
FROM urtable

select * from #tmp
where ID<>myid
*/
DECLARE @ACCTID NCHAR(18)=''
	if(LEN(@InID) = 18)		
		return @InID;
	
	DECLARE @Debug VARCHAR(4000)
	DECLARE @InChars NVARCHAR(32)='ABCDEFGHIJKLMNOPQRSTUVWXYZ012345'
	DECLARE @InUpper NVARCHAR(26)='ABCDEFGHIJKLMNOPQRSTUVWXYZ'
	DECLARE @InI int=15, @InCnt Int=0
	WHILE @InI>0
	BEGIN
		DECLARE @mid nchar(1)=SUBSTRING(@InID, @InI, 1)
		--PRINT 'FINDING MID ' + @mid
		
		--check to see if the current character can be found in inupper as you go one by one
		--comparing binary, if so, return the position found
		DECLARE @InUpperChars int=1
		DECLARE @InStrReturn int=0
		WHILE @InUpperChars<27
		BEGIN
			IF(CONVERT(binary,@mid)=CONVERT(binary,substring(@InUpper,@InUpperChars,1)))
			BEGIN
				SET @InStrReturn=1
				--PRINT 'FOUND ' + @mid + ' AT ' + CONVERT(VARCHAR(250),@InStrReturn)
				BREAK
			END		
			SET @InUpperChars=@InUpperChars+1	
		END
		SET @InCnt = 2 * @InCnt + @InStrReturn
		--PRINT 'InCnt after find is ' + CONVERT(varchar(250),@InCnt)		
		If (@InI % 5) = 1 
		BEGIN
		   SET @ACCTID = SUBSTRING(@InChars, @InCnt + 1, 1) + @ACCTID
		   --Print 'FixId ' + @ACCTID
		   SET @InCnt = 0
		END
		SET @InI=@InI-1
	END 
	SET @ACCTID=@InId+@ACCTID
	--PRINT @ACCTID
	RETURN @ACCTID
	
	
END    	


 
 


rforce2rforce2
Here is a javascript version, I use it on one of my utility pages, and it can be used from within the "Inspect Element" at any time:
 
function convertId(inId){
    if (inId == null)
         return inId;
    inId = inId.replace(/\"/g, "");
    if (inId.length != 15)
        return null;
    var sfx = "";
    for (var i = 0; i < 3; i++) {
        var pos = 0;
        for (var j = 0; j < 5; j++) {
            var c = inId.charAt(i * 5 + j);
            if (c >= "A" && c <= "Z")
                pos += 1 << j;
        }
        if (pos <= 25)
            sfx += "ABCDEFGHIJKLMNOPQRSTUVWXYZ".charAt(pos);
        else
            sfx += "012345".charAt(pos - 26);
    }
    return inId + sfx;
}
 nJoy


Scott CScott C
A proper SQL Server version should have set-based logic instead of a bunch of WHILE loops.  Here is my version.
 
-- Standardize Salesforce Ids
-- Extend case-sensitive 15-char Ids to case-insensitive 18-char
-- Only affects 15-char Ids, 18-char Ids are returned unmodified
CREATE FUNCTION [dbo].[SalesforceId18] ( @Id NCHAR(18) )
RETURNS NCHAR(18) AS BEGIN
    DECLARE @code NCHAR(32) = N'ABCDEFGHIJKLMNOPQRSTUVWXYZ012345' ;
    IF LEN(@Id) = 15
      SELECT @Id = LEFT(@Id, 15)
            + SUBSTRING(@code, 1 + SUM(CASE WHEN ASCII(SUBSTRING(@Id, pos, 1)) BETWEEN 65 AND 90 THEN val ELSE 0 END), 1)
            + SUBSTRING(@code, 1 + SUM(CASE WHEN ASCII(SUBSTRING(@Id, pos + 5, 1)) BETWEEN 65 AND 90 THEN val ELSE 0 END), 1)
            + SUBSTRING(@code, 1 + SUM(CASE WHEN ASCII(SUBSTRING(@Id, pos + 10, 1)) BETWEEN 65 AND 90 THEN val ELSE 0 END), 1)
      FROM (
         SELECT pos = 1, val = 1
         UNION ALL SELECT 2,2
         UNION ALL SELECT 3,4
         UNION ALL SELECT 4,8
         UNION ALL SELECT 5,16
      ) x ;
   RETURN @Id ;
END
 


rockslayerrockslayer
So, I know this is an old thread but I would thought I'd lend some quick insight on the original question...
 
As developers, we often times have a tendancy to make things a bit more complicated than they need to be. If you have a String that needs to represent an Id (18char proper), simply run:
Id properId = Id.valueOf(String s);
 Debug the properId and you will see that SFDC converts the String to 18char Id for you. You can then use String.valueOf() if you need to back-in to String primitive again.


BiswajeetBiswajeet
Hi Astro,
You can try this

String FifteenDigit = 'a0390000009ooJG';
Id EighteenDigit = FifteenDigit;      


RVM GlobalRedRVM GlobalRed
sorry if this code adds to confusion, but thought I'd share a modification of CLRG's SQL function that inputs a table with a 15 digit SFID and outputs 18 digit SFID to specified field

ALTER proc [dbo].[SF_15_to_18]
--Populates @SF18VarName on @tablename with 18 char SFID from @SF15VarName
@tablename varchar(250) ,
    @SF15VarName varchar(250),
    @SF18VarName varchar(250) 
    as

/*Example Call Up:
Execute [dbo].[SF_15_to_18]
@tablename = '[dbo].[CHC_Accounts_20140818_RVM]'
,@SF15VarName = '[Salesforce ID]'
,@SF18VarName = 'SF18'
*/

exec(
'select *
, CAST('''' AS varchar(1)) as  CharHolder
, CAST('''' AS varchar(18)) as  SF18
, 0 as  InCnt
, 0 as  InStrReturn
into #temp from ' + @tablename+

'
/*
alter table #temp
add CharHolder varchar(1)
,SF18 varchar(18)
,InCnt int
,InStrReturn int

update #temp
set CharHolder = '''', SF18 = '''', InCnt = 0, InStrReturn  = 0
*/
DECLARE @Debug VARCHAR(4000)

    DECLARE @InChars NVARCHAR(32)=''ABCDEFGHIJKLMNOPQRSTUVWXYZ012345''

    DECLARE @InUpper NVARCHAR(26)=''ABCDEFGHIJKLMNOPQRSTUVWXYZ''

    DECLARE @InI int=15

    WHILE @InI>0

    BEGIN

  update #temp
        set CharHolder = SUBSTRING([Salesforce id], @InI, 1)

  DECLARE @InUpperChars int=1

  update #temp
  set InStrReturn  = 0

    
   update #temp
   set InStrReturn = case when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,1,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,2,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,3,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,4,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,5,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,6,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,7,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,8,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,9,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,10,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,11,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,12,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,13,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,14,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,15,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,16,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,17,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,18,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,19,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,20,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,21,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,22,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,23,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,24,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,25,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,26,1)) then 1 else InStrReturn end



  update #temp
  SET InCnt = 2 * InCnt + InStrReturn

  If (@InI % 5) = 1
        BEGIN

   update #temp
   SET SF18 = SUBSTRING(@InChars, InCnt + 1, 1) + SF18

   update #temp
   SET InCnt = 0

        END

set @InI = @InI - 1
end



alter table #temp
add sf15 varchar(15)

update #temp
set sf15 = '+ @SF15VarName+'

ALTER TABLE #temp
ALTER COLUMN sf15 VARCHAR(50) COLLATE Latin1_General_CI_AS

update #temp
SET SF18=SF15+SF18



update '+ @tablename+'
set '+@SF18VarName+' = b.SF18
from '+ @tablename+' a
inner join #temp b on (a.'+@SF15VarName+' = b.'+@SF15VarName+')


')


sunny522sunny522
Conersion of 15 digit to 18 digit is simple.
We just need to declare a variable of type Id and then assign the 15 digit number to that variable.

String s15DigitLeadId = '00Q0o00001OoyzD'; //Assume this is record Id of Lead of 15 digits
Id i18DigitId = s15DigitLeadId;
system.debug('Eight Digit Id:'+i18DigitId);
https://salesforceglobe4u.blogspot.com/2018/12/how-to-convert-15-digit-salesforce.html

 


Daniel Banker 11Daniel Banker 11
Teradata

REPLACE FUNCTION sfdc_15id_to_18id ( Id varchar(18))
RETURNS varchar(18)
     LANGUAGE SQL
     DETERMINISTIC
     CONTAINS SQL
     SPECIFIC sfdc_15id_to_18id
     CALLED ON NULL INPUT
     SQL SECURITY DEFINER
     COLLATION INVOKER
     INLINE TYPE 1
RETURN
    case 
    when char_length(Id) = 15
    	then
    	(
          LEFT(Id, 15)
          || SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ012345' from 
				(CASE WHEN ASCII(SUBSTRING(Id from 1 for 1)) BETWEEN 65 AND 90 THEN 1 ELSE 0 END)
            	+ (CASE WHEN ASCII(SUBSTRING(Id from 2 for 1)) BETWEEN 65 AND 90 THEN 2 ELSE 0 END)
            	+ (CASE WHEN ASCII(SUBSTRING(Id from 3 for 1)) BETWEEN 65 AND 90 THEN 4 ELSE 0 END)
            	+ (CASE WHEN ASCII(SUBSTRING(Id from 4 for 1)) BETWEEN 65 AND 90 THEN 8 ELSE 0 END)
            	+ (CASE WHEN ASCII(SUBSTRING(Id from 5 for 1)) BETWEEN 65 AND 90 THEN 16 ELSE 0 END)
            	+ 1
            	for 1)            			            		            			
          || SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ012345' from 
				(CASE WHEN ASCII(SUBSTRING(Id from 6 for 1)) BETWEEN 65 AND 90 THEN 1 ELSE 0 END)
            	+ (CASE WHEN ASCII(SUBSTRING(Id from 7 for 1)) BETWEEN 65 AND 90 THEN 2 ELSE 0 END)
            	+ (CASE WHEN ASCII(SUBSTRING(Id from 8 for 1)) BETWEEN 65 AND 90 THEN 4 ELSE 0 END)
            	+ (CASE WHEN ASCII(SUBSTRING(Id from 9 for 1)) BETWEEN 65 AND 90 THEN 8 ELSE 0 END)
            	+ (CASE WHEN ASCII(SUBSTRING(Id from 10 for 1)) BETWEEN 65 AND 90 THEN 16 ELSE 0 END)
            	+ 1
            	for 1)
          || SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ012345' from 
				(CASE WHEN ASCII(SUBSTRING(Id from 11 for 1)) BETWEEN 65 AND 90 THEN 1 ELSE 0 END)
            	+ (CASE WHEN ASCII(SUBSTRING(Id from 12 for 1)) BETWEEN 65 AND 90 THEN 2 ELSE 0 END)
            	+ (CASE WHEN ASCII(SUBSTRING(Id from 13 for 1)) BETWEEN 65 AND 90 THEN 4 ELSE 0 END)
            	+ (CASE WHEN ASCII(SUBSTRING(Id from 14 for 1)) BETWEEN 65 AND 90 THEN 8 ELSE 0 END)
            	+ (CASE WHEN ASCII(SUBSTRING(Id from 15 for 1)) BETWEEN 65 AND 90 THEN 16 ELSE 0 END)
            	+ 1
            	for 1)
           )
  	 else Id
  	 end
;


 


Christopher ReinerChristopher Reiner
Here's code for R. If 15 digits, it will convert to 18, if 18 it will return the 18 you input:
 

to18Id <- function(inputId){
  if(nchar(inputId)==15){
    #split 15 digit into 3 parts
    chunk1 <- substr(inputId,1,5)
    chunk2 <- substr(inputId,6,10)
    chunk3 <- substr(inputId,11,15)
    
    #reverse each part
    splits <- strsplit(chunk1,"")[[1]]
    chunk1r <- rev(splits)
    splits <- strsplit(chunk2,"")[[1]]
    chunk2r <- rev(splits)
    splits <- strsplit(chunk3,"")[[1]]
    chunk3r <- rev(splits)
    rm(splits)
    
    #replace Uppercase A-Z with 1, all others to 0
    chunk1r[1] <- if_else(grepl("[A-Z]",chunk1r[1]),1,0)
    chunk1r[2] <- if_else(grepl("[A-Z]",chunk1r[2]),1,0)
    chunk1r[3] <- if_else(grepl("[A-Z]",chunk1r[3]),1,0)
    chunk1r[4] <- if_else(grepl("[A-Z]",chunk1r[4]),1,0)
    chunk1r[5] <- if_else(grepl("[A-Z]",chunk1r[5]),1,0)
    chunk2r[1] <- if_else(grepl("[A-Z]",chunk2r[1]),1,0)
    chunk2r[2] <- if_else(grepl("[A-Z]",chunk2r[2]),1,0)
    chunk2r[3] <- if_else(grepl("[A-Z]",chunk2r[3]),1,0)
    chunk2r[4] <- if_else(grepl("[A-Z]",chunk2r[4]),1,0)
    chunk2r[5] <- if_else(grepl("[A-Z]",chunk2r[5]),1,0)
    chunk3r[1] <- if_else(grepl("[A-Z]",chunk3r[1]),1,0)
    chunk3r[2] <- if_else(grepl("[A-Z]",chunk3r[2]),1,0)
    chunk3r[3] <- if_else(grepl("[A-Z]",chunk3r[3]),1,0)
    chunk3r[4] <- if_else(grepl("[A-Z]",chunk3r[4]),1,0)
    chunk3r[5] <- if_else(grepl("[A-Z]",chunk3r[5]),1,0)
    
    #paste back to a single chunks
    chunk1r <- paste(chunk1r, collapse = "")  
    chunk2r <- paste(chunk2r, collapse = "")    
    chunk3r <- paste(chunk3r, collapse = "")  
    
    #lookup tables
    vals <- c("A","B","C","D","E","F","G","H",
              "I","J","K","L","M","N","O","P",
              "Q","R","S","T","U","V","W","X",
              "Y","Z",0,1,2,3,4,5)
    bin_vals <- c("00000","00001","00010","00011","00100",
                  "00101","00110","00111","01000","01001",
                  "01010","01011","01100","01101","01110",
                  "01111","10000","10001","10010","10011",
                  "10100","10101","10110","10111","11000",
                  "11001","11010","11011","11100","11101",
                  "11110","11111")
    
    newIdsuffix <- paste0(vals[match(chunk1r,bin_vals)],vals[match(chunk2r,bin_vals)], vals[match(chunk3r,bin_vals)])
    return(paste0(inputId,newIdsuffix))
  }
  return(inputId)
}


 


Ad CadAd Cad
Hi,

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!

Adam


Amrit Singh 27Amrit Singh 27
A python version if anyone needs it:
 

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


 


Daniel Nuñez 17Daniel Nuñez 17
I fixed the VBA Version that can be used in Excel
 

Function FixID(InID As String) As String
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


 


Graham MartinGraham Martin
Version for use in Power Query
Taken From https://gist.github.com/grenzi/78eee1863130c29c6689ed27cb0de4bb

(sf15Id as any) => let
        Source = sf15Id,
        #"tab" = #table(1, {{Source}}),
        #"cb1" = Table.AddColumn(#"tab", "cb1", each if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Text.Middle([Column1],0,1)) then 1 else 0),
        #"cb2" = Table.AddColumn(#"cb1", "cb2", each if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Text.Middle([Column1],1,1)) then 2 else 0),
        #"cb3" = Table.AddColumn(#"cb2", "cb3", each if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Text.Middle([Column1],2,1)) then 4 else 0),
        #"cb4" = Table.AddColumn(#"cb3", "cb4", each if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Text.Middle([Column1],3,1)) then 8 else 0),
        #"cb5" = Table.AddColumn(#"cb4", "cb5", each if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Text.Middle([Column1],4,1)) then 16 else 0),
        #"char16" = Table.AddColumn(cb5, "char16", each Text.Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345", [cb1]+[cb2]+[cb3]+[cb4]+[cb5], 1)),
        #"cb6" = Table.AddColumn(#"char16", "cb6", each if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Text.Middle([Column1],5,1)) then 1 else 0),
        #"cb7" = Table.AddColumn(#"cb6", "cb7", each if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Text.Middle([Column1],6,1)) then 2 else 0),
        #"cb8" = Table.AddColumn(#"cb7", "cb8", each if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Text.Middle([Column1],7,1)) then 4 else 0),
        #"cb9" = Table.AddColumn(#"cb8", "cb9", each if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Text.Middle([Column1],8,1)) then 8 else 0),
        #"cb10" = Table.AddColumn(#"cb9", "cb10", each if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Text.Middle([Column1],9,1)) then 16 else 0),
        #"char17" = Table.AddColumn(cb10, "char17", each Text.Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345", [cb6]+[cb7]+[cb8]+[cb9]+[cb10], 1)),
        #"cb11" = Table.AddColumn(#"char17", "cb11", each if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Text.Middle([Column1],10,1)) then 1 else 0),
        #"cb12" = Table.AddColumn(#"cb11", "cb12", each if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Text.Middle([Column1],11,1)) then 2 else 0),
        #"cb13" = Table.AddColumn(#"cb12", "cb13", each if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Text.Middle([Column1],12,1)) then 4 else 0),
        #"cb14" = Table.AddColumn(#"cb13", "cb14", each if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Text.Middle([Column1],13,1)) then 8 else 0),
        #"cb15" = Table.AddColumn(#"cb14", "cb15", each if Text.Contains("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Text.Middle([Column1],14,1)) then 16 else 0),
        #"char18" = Table.AddColumn(cb15, "char18", each Text.Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345", [cb11]+[cb12]+[cb13]+[cb14]+[cb15], 1)),
        #"Added Custom" = Table.AddColumn(char18, "sf18id", each [Column1]&[char16]&[char17]&[char18]),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"sf18id"})
    in
        #"Removed Other Columns"