I have an Excel spreadsheet with a large number of rows. Each row includes the 15 character case sensitive version of a Salesforce Id. I need to find the rows that contain duplicate Salesforce Ids.
With the 15 character Salesforce Ids being case sensitive, and Excel by and large being case insensitive this presents an issue.
- Export the data from Salesforce using the 18 character Salesforce Ids. These are specifically designed to be case insensitive.
- Use an Excel formula to create another column containing a case insensitive value.
=CD2&CODE(MID(CD2,12,1))&CODE(MID(CD2,13,1))&CODE(MID(CD2,14,1))&CODE(MID(CD2,15,1))Here the cell CD2 contains the case sensitive Salesforce Id.
This formula is appending the ASCII character value for each of the last 4 characters in the ID.
For example, an ID that ends with "yBSe" will have "1216683101" appended.
You may need to extend the number of checked characters if you have a large number of records. As it is only the last 4 characters of the input Id will be case checked.
- Use VBA to calculate the 18 character case insensitive values.
Place the following into a VBA module.
Function FixID(InID As String) As String ' Converts a 15 character ID to an 18 character, case-insensitive one ... Dim InChars As String, InI As Integer, InUpper As String Dim InCnt As Integer InChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345" InUpper = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" InCnt = 0 For InI = 15 To 1 Step -1 InCnt = 2 * InCnt + Sgn(InStr(1, InUpper, Mid(InID, InI, 1), vbBinaryCompare)) If InI Mod 5 = 1 Then FixID = Mid(InChars, InCnt + 1, 1) + FixID InCnt = 0 End If Next InI FixID = InID + FixID End Function
Then reference the function directly in a formula.
- Eat The Bear: Case sensitive VLOOKUP search in MS Excel