Converting IDs in Excel, without code

Blog: Salesforce #DataTipOfTheDay, Tips & Tricks, and Whatever Else‎ > ‎

posted Dec 13, 2013, 9:22 AM by Ezra Kenigsberg [ updated Dec 13, 2013, 9:28 AM ]

#DataTipOfTheDay 2013-12-13

Q: "How can I convert a 15-character Salesforce ID to 18 characters in Excel? Without VBA code?"

A: Use the formula below (assuming the 15-character ID is in cell A1).

If your PC can run VBA code, I encourage you to copy this add-in to your PC's XLSTART directory and use the FixID() function instead. (Your XLSTART directory is

C:\Users\[username]\AppData\Roaming\Microsoft\Excel\XLSTART--for Win7 and Win8--and
C:\Documents and Settings\[username]\Application Data\Microsoft\Excel\XLSTART--for Win XP.)

Remember too that you can create a formula field in Salesforce that converts 15-character IDs to 18 characters using the CASESAFEID() function.

(Salesforce's 18-character IDs aren't totally case-safe, but that's a harangue for another day.)

=A1 &

MID(

"ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",

MIN(IF(OR(MID(A1, 5, 1)<65, MID(A1, 5, 1)>90), 0, FIND(MID(A1, 5, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 16 +

MIN(IF(OR(MID(A1, 4, 1)<65, MID(A1, 4, 1)>90), 0, FIND(MID(A1, 4, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 8 +

MIN(IF(OR(MID(A1, 3, 1)<65, MID(A1, 3, 1)>90), 0, FIND(MID(A1, 3, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 4 +

MIN(IF(OR(MID(A1, 2, 1)<65, MID(A1, 2, 1)>90), 0, FIND(MID(A1, 2, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 2 +

MIN(IF(OR(MID(A1, 1, 1)<65, MID(A1, 1, 1)>90), 0, FIND(MID(A1, 1, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 1 + 1,

1) &

MID(

"ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",

MIN(IF(OR(MID(A1, 10, 1)<65, MID(A1, 10, 1)>90), 0, FIND(MID(A1, 10, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 16 +

MIN(IF(OR(MID(A1, 9, 1)<65, MID(A1, 9, 1)>90), 0, FIND(MID(A1, 9, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 8 +

MIN(IF(OR(MID(A1, 8, 1)<65, MID(A1, 8, 1)>90), 0, FIND(MID(A1, 8, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 4 +

MIN(IF(OR(MID(A1, 7, 1)<65, MID(A1, 7, 1)>90), 0, FIND(MID(A1, 7, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 2 +

MIN(IF(OR(MID(A1, 6, 1)<65, MID(A1, 6, 1)>90), 0, FIND(MID(A1, 6, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 1 + 1,

1) &

MID(

"ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",

MIN(IF(OR(MID(A1, 15, 1)<65, MID(A1, 15, 1)>90), 0, FIND(MID(A1, 15, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 16 +

MIN(IF(OR(MID(A1, 14, 1)<65, MID(A1, 14, 1)>90), 0, FIND(MID(A1, 14, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 8 +

MIN(IF(OR(MID(A1, 13, 1)<65, MID(A1, 13, 1)>90), 0, FIND(MID(A1, 13, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 4 +

MIN(IF(OR(MID(A1, 12, 1)<65, MID(A1, 12, 1)>90), 0, FIND(MID(A1, 12, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 2 +

MIN(IF(OR(MID(A1, 11, 1)<65, MID(A1, 11, 1)>90), 0, FIND(MID(A1, 11, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1) * 1 + 1,

1)

Comments

	Search this site