Apex Code Development You need to sign in to do that
Don't have an account?
B kld Convert 18 digit ID to 15 digit ID in Excel
Immediate Requirement plz give approaches
I have Exported Account object records using DataLoader. ID's in the csv(Excel) file have (a0r90000008cJzaAAE) 18 digit.But i want those as (a0r90000008cJza)15 digit in the same sheet or other Excel Sheet.
I write formula "=LEFT(A2,15)", it was truncated but when i tried to copy those cell to other Excel sheet It's getting nothing like blank because it was a formula cell.
What to do?
Plz give me approaches
I have Exported Account object records using DataLoader. ID's in the csv(Excel) file have (a0r90000008cJzaAAE) 18 digit.But i want those as (a0r90000008cJza)15 digit in the same sheet or other Excel Sheet.
I write formula "=LEFT(A2,15)", it was truncated but when i tried to copy those cell to other Excel sheet It's getting nothing like blank because it was a formula cell.
What to do?
Plz give me approaches
I found this here (http://salesforce.stackexchange.com/questions/1653/what-are-salesforce-ids-composed-of)
"The algorithm to convert from a 15 character Id to an 18 character Id is: (Source (http://boards.developerforce.com/t5/General-Development/GetUserInfoResult-getOrganizationId-does-not-equal-OrgID/m-p/134530)- I'm sure there used to be official documentation on how do this.)
1. Divide the 15 char into 3 chunks of 5 chars each.
2. For each character give that position a value of 1 if uppercase, 0 otherwise (lowercase or number).
2. Combine the bits from each chunk into a 5 bit integer where the rightmost bit is the most significant bit. This will yield a number between 0 and 31 for each chunk.
4. Construct an array that contains the sequence of capital letters A-Z and 0-5 (26 + 6 = 32 possible values).
5. Use the integer from each chunk to choose a character from the array.
6. Append the resulting 3 characters, in chunk order, to the end of the 15 char id.
In a formula there is the CASESAFEID function that will perform this algorithm.
You can apply this algorithm to some sample IDs to see how it doesn't really function as a checksum or checkdigit. For example, if you exclude the alpha characters, every ID between 001100000000001 and 001999999999999 will have the suffix AAA. Infact, you get the same suffix if you include any lowercase alpha characters as well. The suffix will only change in the presence of uppercase characters. It is basically encoding which of the 5 characters that each suffix character represents are uppercase."
I hope that helps you :D
to convert them with google sheet we use this steps:
mixed length IDs in column A
Column B lenght formua to find lenght : =LEN(A:A) copy down..
Add filter & Filter by 15 to get all ID 15 long.
Go to tools -> script editor copy paste this code:
function SFConvert18(value) {
if (value.length == 15){
var convertedID = "";
for (var i = 0; i < 3; i++){
var baseTwo=0;
for (var j = 0; j < 5; j++){
var character = value.charAt(i*5+j);
if(character >= "A" && character<="Z"){
baseTwo+=1<<j;
}
}
convertedID+="ABCDEFGHIJKLMNOPQRSTUVWXYZ012345".charAt(baseTwo);
}
return value + convertedID;
}
else{
return "Error: Please enter a valid Salesforce ID with 15 characters";
}
}
function SFConvert15(value){
if (value.length == 18){
return value.substring(0, 15);
}
else{
return "Error: Please enter a valid Salesforce ID with 18 characters";
}
}
In Column C paste this: =SFCONVERT18($A) - copy down...
now you will have in 19 long IDs in column C.
to copy only 18 long ID to any other Column use this: =IF( LEN(A2) = 18,A 2, C2) - copy down....