Skip to content

Instantly share code, notes, and snippets.

@kenandersen

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

Created December 29, 2014 22:51
Show Gist options
  • Save kenandersen/89db9c8391848cd7d068 to your computer and use it in GitHub Desktop.
Save kenandersen/89db9c8391848cd7d068 to your computer and use it in GitHub Desktop.
Download ZIP
Formula 1 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 1.md

=IF(LEN(A2)=18,

A2,

IF(LEN(A2)=15,

CONCATENATE(

(IF((EXACT(LOWER((RIGHT(LEFT(A2,5),1))),(RIGHT(LEFT(A2,5),1)))),0,1) &IF((EXACT(LOWER((RIGHT(LEFT(A2,4),1))),(RIGHT(LEFT(A2,4),1)))),0,1) &IF((EXACT(LOWER((RIGHT(LEFT(A2,3),1))),(RIGHT(LEFT(A2,3),1)))),0,1) &IF((EXACT(LOWER((RIGHT(LEFT(A2,2),1))),(RIGHT(LEFT(A2,2),1)))),0,1) &IF((EXACT(LOWER((RIGHT(LEFT(A2,1),1))),(RIGHT(LEFT(A2,1),1)))),0,1)),

(IF((EXACT(LOWER(MID(A2,10,1)),(MID(A2,10,1)))),0,1) &IF((EXACT(LOWER(MID(A2,9,1)),(MID(A2,9,1)))),0,1) &IF((EXACT(LOWER(MID(A2,8,1)),(MID(A2,8,1)))),0,1) &IF((EXACT(LOWER(MID(A2,7,1)),(MID(A2,7,1)))),0,1) &IF((EXACT(LOWER(MID(A2,6,1)),(MID(A2,6,1)))),0,1)),

(IF((EXACT(LOWER((LEFT(RIGHT(A2,1),1))),(LEFT(RIGHT(A2,1),1)))),0,1) &IF((EXACT(LOWER((LEFT(RIGHT(A2,2),1))),(LEFT(RIGHT(A2,2),1)))),0,1) &IF((EXACT(LOWER((LEFT(RIGHT(A2,3),1))),(LEFT(RIGHT(A2,3),1)))),0,1) &IF((EXACT(LOWER((LEFT(RIGHT(A2,4),1))),(LEFT(RIGHT(A2,4),1)))),0,1) &IF((EXACT(LOWER((LEFT(RIGHT(A2,5),1))),(LEFT(RIGHT(A2,5),1)))),0,1)))))

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