Inline 18 digit Salesforce ID Excel formula

If you have not got the ability to create a Salesforce.com custom field to generate the 18 digit case insensetive ID, as demonstrated here, you can use this formula to convert the ID in "A2" to the 18 digit ID. Of course you can then extend this formula all the way down column A if you need to.


=A2 & LOOKUP(IF(AND(CODE(RIGHT(LEFT($A2,5),1))>=65,CODE(RIGHT(LEFT($A2,5),1))<=90),1,0) & IF(AND(CODE(RIGHT(LEFT($A2,4),1))>=65,CODE(RIGHT(LEFT($A2,4),1))<=90),1,0) & IF(AND(CODE(RIGHT(LEFT($A2,3),1))>=65,CODE(RIGHT(LEFT($A2,3),1))<=90),1,0)& IF(AND(CODE(RIGHT(LEFT($A2,2),1))>=65,CODE(RIGHT(LEFT($A2,2),1))<=90),1,0)& IF(AND(CODE(LEFT($A2,1))>=65,CODE(LEFT($A2,1))<=90),1,0),{"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"},{"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"}) & LOOKUP(IF(AND(CODE(RIGHT(LEFT($A2,10),1))>=65,CODE(RIGHT(LEFT($A2,10),1))<=90),1,0) & IF(AND(CODE(RIGHT(LEFT($A2,9),1))>=65,CODE(RIGHT(LEFT($A2,9),1))<=90),1,0) & IF(AND(CODE(RIGHT(LEFT($A2,8),1))>=65,CODE(RIGHT(LEFT($A2,8),1))<=90),1,0)& IF(AND(CODE(RIGHT(LEFT($A2,7),1))>=65,CODE(RIGHT(LEFT($A2,7),1))<=90),1,0)& IF(AND(CODE(RIGHT(LEFT($A2,6),1))>=65,CODE(RIGHT(LEFT($A2,6),1))<=90),1,0),{"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"},{"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"})  & LOOKUP(IF(AND(CODE(RIGHT(LEFT($A2,15),1))>=65,CODE(RIGHT(LEFT($A2,15),1))<=90),1,0) & IF(AND(CODE(RIGHT(LEFT($A2,14),1))>=65,CODE(RIGHT(LEFT($A2,14),1))<=90),1,0) & IF(AND(CODE(RIGHT(LEFT($A2,13),1))>=65,CODE(RIGHT(LEFT($A2,13),1))<=90),1,0)& IF(AND(CODE(RIGHT(LEFT($A2,12),1))>=65,CODE(RIGHT(LEFT($A2,12),1))<=90),1,0)& IF(AND(CODE(RIGHT(LEFT($A2,11),1))>=65,CODE(RIGHT(LEFT($A2,11),1))<=90),1,0),{"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"},{"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"})

Comments

  1. UnknownApril 10, 2015 at 6:42 AM

    Nice Post

    ReplyDelete
  2. JFordAugust 26, 2016 at 7:09 AM

    Thank you for posting this - I come to it a few times a week!

    ReplyDelete
  3. AnonymousMarch 14, 2017 at 6:23 AM

    You rock, my old copy of this formula was corrupted. Glad someone is posting it out there for us common people.

    ReplyDelete

Post a Comment

Popular posts from this blog

Salesforce Merge Accounts URL Hacking

The Merge Accounts page is OK to use, however to start the process you have to search for the Accounts you want to merge, and to do that they have to have the same name. This is not always the case, and it can be annoying to have to rename an account, just so you can find it to merge it. The official SFDC selection screen also limits you to a max of 3 accounts. This isn't actually a hard limit... More of a guide, as you'll see below. Instead of this, you can actually hack the URL to include the IDs of any accounts, regardless of Account Name similarity. This is especially useful if you are using your own code to manage merges and would like to "deep link" into the SFDC platform. Here's an example URL: https://emea.salesforce.com/merge/accmergewizard.jsp?goNext=+Next+&cid=001200000012345&cid=001200000012346&cid=001200000012347&cid=001200000012348 It can be broken down into 3 main segments: The Merge Accounts page URL: https://emea.sale...
Read more

Australian Postcode Boundaries for Google Earth (KMZ)

Image
I searched for ages, and couldn't find where to get a KMZ / KML file for Google Earth that had all the Australian Postcode Boundaries in it... So I made one based on the 1270.0.55.003 - Australian Statistical Geography Standard (ASGS): Volume 3 - Non ABS Structures, July 2011 . I claim no additional copyright on this, but please ensure you are able to use the data according to the ABS Copyright rules Here is the download link
Read more

Don't look up!

Image
Don't look up people. The Feces is about to hit the rotating ceiling appliance. (for those of you requiring monosyllabic prose, this means the shit is about to hit the fan) It would appear that the almighty ACMA ( Australian Censorship Management Authority ) blacklist of websites has been leaked online. It had to happen, and it is better it happens now. The saviour of free speech , WikiLeaks (Not to be confused with Wikipedia), has posted it online. Interestingly, I cannot access WikiLeaks at the moment, this could be due to a crap internet connection, or perhaps it is a little more clandestine than that (do I hear black helicopters?). According to the Sydney Morning Herald , who have apparently seen the list, the below are some of the more interesting things on the blacklist: A Queensland dentist (Not illegal) A tour operator (Not illegal) Several Christian sites (Not illegal) Fetish sites (Not illegal) Fringe religion sites, such as satanism (Not illegal) Euthanasia how-to sites...
Read more