Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
astro

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

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.

Shikibu

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

BritishBoyinDC

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

BritishBoyinDC

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

astro

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
astro

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

.......

Shikibu
Have you tried using touppercase or tolowercase?
Superfell
Can you backup a step and explain why you need this ?
astro

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.

Shikibu

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.

astro

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!

Shikibu
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).
astro

Yes very good point, I have told them to be aware of this.

argh integrations :)

SD_Chargers

Hi Astro,

Did you get find a solution to how to convert 15 digit IDs to 18?

SD

TaoDuh

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.

CAfromCA

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.

ErikMittmeyer

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﻿

d19engel

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

Hehe, I like that solution! Works fine for me too! ;)

Ankur_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

collie

I need a quick and easy solution. thanks

Starz26

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

Starz26

collie wrote:

I need a quick and easy solution. thanks

Um, Just upsert the data back using dataloader

jwalsh

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

CLRG

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

```

rforce2

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

rockslayer

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.

Biswajeet
Hi Astro,
You can try this

String FifteenDigit = 'a0390000009ooJG';
Id EighteenDigit = FifteenDigit;
RVM 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
,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

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+')

')
sunny522
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
system.debug('Eight Digit Id:'+i18DigitId);
https://salesforceglobe4u.blogspot.com/2018/12/how-to-convert-15-digit-salesforce.html

Daniel Banker 11
```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 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)
}```

Hi,

If you want to do ad-hoc conversions of Id's, rather than programatically, then this Chrome extension makes it easy:

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!

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