Skip to content

Instantly share code, notes, and snippets.

@kenandersen

kenandersen/Excel Formula to Convert 15-digit Salesforce IDs to 18-digit - Part 2.md

Created December 29, 2014 22:54
Show Gist options
  • Save kenandersen/4836c2c2489b814fd645 to your computer and use it in GitHub Desktop.
Save kenandersen/4836c2c2489b814fd645 to your computer and use it in GitHub Desktop.
Download ZIP
Formula 2 of 2 to convert a 15-digit ID to a 18-digit ID
Raw
Excel Formula to Convert 15-digit Salesforce IDs to 18-digit - Part 2.md

= IF(LEN(A2)=18,

A2,

IF(LEN(A2)=15,

CONCATENATE(A2, (IF(LEFT(B2,5)="00000", "A", IF(LEFT(B2,5)="00001", "B", IF(LEFT(B2,5)="00010", "C", IF(LEFT(B2,5)="00011", "D", IF(LEFT(B2,5)="00100", "E", IF(LEFT(B2,5)="00101", "F", IF(LEFT(B2,5)="00110", "G", IF(LEFT(B2,5)="00111", "H", IF(LEFT(B2,5)="01000", "I", IF(LEFT(B2,5)="01001", "J", IF(LEFT(B2,5)="01010", "K", IF(LEFT(B2,5)="01011", "L", IF(LEFT(B2,5)="01100", "M", IF(LEFT(B2,5)="01101", "N", IF(LEFT(B2,5)="01110", "O", IF(LEFT(B2,5)="01111", "P", IF(LEFT(B2,5)="10000", "Q", IF(LEFT(B2,5)="10001", "R", IF(LEFT(B2,5)="10010", "S", IF(LEFT(B2,5)="10011", "T", IF(LEFT(B2,5)="10100", "U", IF(LEFT(B2,5)="10101", "V", IF(LEFT(B2,5)="10110", "W", IF(LEFT(B2,5)="10111", "X", IF(LEFT(B2,5)="11000", "Y", IF(LEFT(B2,5)="11001", "Z", IF(LEFT(B2,5)="11010", "0", IF(LEFT(B2,5)="11011", "1", IF(LEFT(B2,5)="11100", "2", IF(LEFT(B2,5)="11101", "3", IF(LEFT(B2,5)="11110", "4", IF(LEFT(B2,5)="11111", "5", "UNRECOGNIZED STRING"

) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) & IF(MID(B2,6,5)="00000", "A", IF(MID(B2,6,5)="00001", "B", IF(MID(B2,6,5)="00010", "C", IF(MID(B2,6,5)="00011", "D", IF(MID(B2,6,5)="00100", "E", IF(MID(B2,6,5)="00101", "F", IF(MID(B2,6,5)="00110", "G", IF(MID(B2,6,5)="00111", "H", IF(MID(B2,6,5)="01000", "I", IF(MID(B2,6,5)="01001", "J", IF(MID(B2,6,5)="01010", "K", IF(MID(B2,6,5)="01011", "L", IF(MID(B2,6,5)="01100", "M", IF(MID(B2,6,5)="01101", "N", IF(MID(B2,6,5)="01110", "O", IF(MID(B2,6,5)="01111", "P", IF(MID(B2,6,5)="10000", "Q", IF(MID(B2,6,5)="10001", "R", IF(MID(B2,6,5)="10010", "S", IF(MID(B2,6,5)="10011", "T", IF(MID(B2,6,5)="10100", "U", IF(MID(B2,6,5)="10101", "V", IF(MID(B2,6,5)="10110", "W", IF(MID(B2,6,5)="10111", "X", IF(MID(B2,6,5)="11000", "Y", IF(MID(B2,6,5)="11001", "Z", IF(MID(B2,6,5)="11010", "0", IF(MID(B2,6,5)="11011", "1", IF(MID(B2,6,5)="11100", "2", IF(MID(B2,6,5)="11101", "3", IF(MID(B2,6,5)="11110", "4", IF(MID(B2,6,5)="11111", "5", "UNRECOGNIZED STRING"

) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) & IF(RIGHT(B2,5)="00000", "A", IF(RIGHT(B2,5)="00001", "B", IF(RIGHT(B2,5)="00010", "C", IF(RIGHT(B2,5)="00011", "D", IF(RIGHT(B2,5)="00100", "E", IF(RIGHT(B2,5)="00101", "F", IF(RIGHT(B2,5)="00110", "G", IF(RIGHT(B2,5)="00111", "H", IF(RIGHT(B2,5)="01000", "I", IF(RIGHT(B2,5)="01001", "J", IF(RIGHT(B2,5)="01010", "K", IF(MID(B2,6,5)="01011", "L", IF(RIGHT(B2,5)="01100", "M", IF(RIGHT(B2,5)="01101", "N", IF(RIGHT(B2,5)="01110", "O", IF(RIGHT(B2,5)="01111", "P", IF(RIGHT(B2,5)="10000", "Q", IF(RIGHT(B2,5)="10001", "R", IF(RIGHT(B2,5)="10010", "S", IF(RIGHT(B2,5)="10011", "T", IF(RIGHT(B2,5)="10100", "U", IF(RIGHT(B2,5)="10101", "V", IF(RIGHT(B2,5)="10110", "W", IF(RIGHT(B2,5)="10111", "X", IF(RIGHT(B2,5)="11000", "Y", IF(RIGHT(B2,5)="11001", "Z", IF(RIGHT(B2,5)="11010", "0", IF(RIGHT(B2,5)="11011", "1", IF(RIGHT(B2,5)="11100", "2", IF(RIGHT(B2,5)="11101", "3", IF(RIGHT(B2,5)="11110", "4", IF(RIGHT(B2,5)="11111", "5", "UNRECOGNIZED STRING"

) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) )))))

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment