Excel Macro: Bulk Validate EIDR and ISAN Identifiers in Your Title List

Every title in a content catalog needs a unique, standardized identifier. EIDR (Entertainment Identifier Registry) and ISAN (International Standard Audiovisual Number) are the two dominant standards. EIDR assigns a DOI-based ID to every movie, episode, and edit. ISAN, governed by ISO 15706, provides a similar function with a hexadecimal structure. When you deliver content to platforms like Netflix, Amazon, or Apple TV, your metadata must include valid identifiers. If an EIDR is malformed, the platform cannot match your delivery to its catalog entry. The content either fails ingestion entirely or gets orphaned in a queue. If the identifier is wrong rather than just malformed, revenue from that title may be attributed to someone else.

This tutorial gives you a VBA macro that scans an entire column of identifiers, validates each one, and flags every problem so you can fix your catalog before delivery.

Section 1: EIDR Format and Validator

An EIDR ID follows this structure:

10.5240/XXXX-XXXX-XXXX-XXXX-XXXX-C

It starts with the DOI prefix 10.5240/, followed by five groups of four hexadecimal characters separated by hyphens, and ends with a single check character. The check character is computed using ISO 7064 Mod 37,36 over the 20 hex digits of the suffix.

Open the VBA editor in Excel (Alt + F11), insert a new module (Insert > Module), and add the following function:

Function IsValidEIDR(eidr As String) As String
    Dim cleaned As String
    cleaned = Trim(eidr)

    ' Check basic length and prefix
    If Len(cleaned) <> 34 Then
        IsValidEIDR = "FORMAT_ERROR: Expected 34 characters, got " & Len(cleaned)
        Exit Function
    End If

    If Left(cleaned, 8) <> "10.5240/" Then
        IsValidEIDR = "FORMAT_ERROR: Must start with 10.5240/"
        Exit Function
    End If

    ' Extract suffix: XXXX-XXXX-XXXX-XXXX-XXXX-C
    Dim suffix As String
    suffix = Mid(cleaned, 9)

    ' Validate hyphen positions (5,10,15,20,25)
    Dim hyphens As Variant
    hyphens = Array(5, 10, 15, 20, 25)
    Dim h As Variant
    For Each h In hyphens
        If Mid(suffix, h, 1) <> "-" Then
            IsValidEIDR = "FORMAT_ERROR: Missing hyphen at position " & (h + 8)
            Exit Function
        End If
    Next h

    ' Extract the 20 hex digits and check character
    Dim hexPart As String
    hexPart = Replace(Mid(suffix, 1, 24), "-", "")

    If Len(hexPart) <> 20 Then
        IsValidEIDR = "FORMAT_ERROR: Expected 20 hex digits in suffix"
        Exit Function
    End If

    ' Validate all characters are hex
    Dim i As Long
    Dim c As String
    For i = 1 To Len(hexPart)
        c = UCase(Mid(hexPart, i, 1))
        If InStr("0123456789ABCDEF", c) = 0 Then
            IsValidEIDR = "FORMAT_ERROR: Invalid hex character '" & Mid(hexPart, i, 1) & "'"
            Exit Function
        End If
    Next i

    ' Compute ISO 7064 Mod 37,36 check character
    Dim charset As String
    charset = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    Dim product As Long
    product = 36

    For i = 1 To 20
        c = UCase(Mid(hexPart, i, 1))
        Dim charVal As Long
        charVal = InStr(charset, c) - 1
        product = (product + charVal) Mod 37
        If product = 0 Then product = 37
        product = (product * 2) Mod 38
    Next i

    Dim checkVal As Long
    checkVal = (38 - product) Mod 37
    Dim expectedCheck As String
    If checkVal < 36 Then
        expectedCheck = Mid(charset, checkVal + 1, 1)
    Else
        expectedCheck = "+"
    End If

    Dim actualCheck As String
    actualCheck = UCase(Right(suffix, 1))

    If actualCheck <> expectedCheck Then
        IsValidEIDR = "CHECKSUM_ERROR: Expected '" & expectedCheck & "', got '" & actualCheck & "'"
        Exit Function
    End If

    IsValidEIDR = "VALID"
End Function

The function returns "VALID" on success or a descriptive error string. This makes it usable both as a worksheet formula and from the bulk macro.

Section 2: ISAN Format and Validator

An ISAN looks like this when formatted with hyphens:

ISAN XXXX-XXXX-XXXX-XXXX-X

The core is 16 hexadecimal digits (the root and episode segments) followed by a single check character. The check character is calculated using ISO 7064 Mod 11,2 over the numeric values of the 16 hex digits. Some ISANs include a version segment (an additional 8 hex digits and check), but the root+episode+check is the minimum valid form.

Add this function to the same module:

Function IsValidISAN(isan As String) As String
    Dim cleaned As String
    cleaned = UCase(Trim(isan))

    ' Strip the "ISAN" prefix if present
    If Left(cleaned, 5) = "ISAN " Then
        cleaned = Mid(cleaned, 6)
    ElseIf Left(cleaned, 4) = "ISAN" Then
        cleaned = Mid(cleaned, 5)
    End If

    ' Remove hyphens and spaces
    cleaned = Replace(Replace(cleaned, "-", ""), " ", "")

    ' Must be at least 17 characters (16 hex + 1 check)
    If Len(cleaned) < 17 Then
        IsValidISAN = "FORMAT_ERROR: Too short. Need at least 16 hex digits + check character."
        Exit Function
    End If

    ' Extract root+episode (first 16 chars) and check (17th char)
    Dim hexPart As String
    hexPart = Left(cleaned, 16)
    Dim checkChar As String
    checkChar = Mid(cleaned, 17, 1)

    ' Validate hex characters
    Dim i As Long
    Dim c As String
    For i = 1 To 16
        c = Mid(hexPart, i, 1)
        If InStr("0123456789ABCDEF", c) = 0 Then
            IsValidISAN = "FORMAT_ERROR: Invalid hex character '" & Mid(hexPart, i, 1) & "' at position " & i
            Exit Function
        End If
    Next i

    ' Compute ISO 7064 Mod 11,2 check digit
    Dim sum As Long
    sum = 0
    For i = 1 To 16
        c = Mid(hexPart, i, 1)
        Dim val As Long
        If c >= "0" And c <= "9" Then
            val = Asc(c) - Asc("0")
        Else
            val = Asc(c) - Asc("A") + 10
        End If
        sum = sum + val
        If sum Mod 2 = 0 Then
            sum = sum \ 2
        Else
            sum = (sum + 11) \ 2
        End If
    Next i

    Dim remainder As Long
    remainder = (12 - (sum Mod 11)) Mod 11

    Dim expectedCheck As String
    If remainder = 10 Then
        expectedCheck = "X"
    Else
        expectedCheck = CStr(remainder)
    End If

    If checkChar <> expectedCheck Then
        IsValidISAN = "CHECKSUM_ERROR: Expected '" & expectedCheck & "', got '" & checkChar & "'"
        Exit Function
    End If

    IsValidISAN = "VALID"
End Function

Section 3: Bulk Validation Macro

Now add the macro that ties everything together. It scans a column you specify, auto-detects whether each cell contains an EIDR or ISAN, validates it, and marks problems directly in the spreadsheet.

Sub ValidateIdentifiers()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    Dim idColumn As Long
    idColumn = Application.InputBox("Enter the column number containing identifiers (e.g., 1 for A, 3 for C):", Type:=1)
    If idColumn = 0 Then Exit Sub

    Dim startRow As Long
    startRow = Application.InputBox("Enter the first data row (e.g., 2 if row 1 is headers):", Type:=1)
    If startRow = 0 Then Exit Sub

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, idColumn).End(xlUp).Row

    Dim validCount As Long, invalidCount As Long, missingCount As Long
    validCount = 0: invalidCount = 0: missingCount = 0

    ' Clear previous formatting and comments in the column
    ws.Range(ws.Cells(startRow, idColumn), ws.Cells(lastRow, idColumn)).Interior.ColorIndex = xlNone
    On Error Resume Next
    ws.Range(ws.Cells(startRow, idColumn), ws.Cells(lastRow, idColumn)).ClearComments
    On Error GoTo 0

    Dim i As Long
    For i = startRow To lastRow
        Dim cellVal As String
        cellVal = Trim(CStr(ws.Cells(i, idColumn).Value))

        ' Skip empty cells
        If Len(cellVal) = 0 Then
            missingCount = missingCount + 1
            ws.Cells(i, idColumn).Interior.Color = RGB(255, 255, 200) ' Light yellow
            ws.Cells(i, idColumn).AddComment "MISSING: No identifier in this cell."
            GoTo NextRow
        End If

        Dim result As String

        ' Auto-detect identifier type
        If Left(cellVal, 7) = "10.5240" Then
            result = IsValidEIDR(cellVal)
        ElseIf UCase(Left(cellVal, 4)) = "ISAN" Or IsHexString(Replace(Replace(Replace(cellVal, "-", ""), " ", ""), "ISAN", "")) Then
            result = IsValidISAN(cellVal)
        Else
            result = "FORMAT_ERROR: Unrecognized identifier format. Expected EIDR (10.5240/...) or ISAN."
        End If

        If result = "VALID" Then
            validCount = validCount + 1
            ws.Cells(i, idColumn).Interior.Color = RGB(200, 255, 200) ' Light green
        Else
            invalidCount = invalidCount + 1
            ws.Cells(i, idColumn).Interior.Color = RGB(255, 200, 200) ' Light red
            ws.Cells(i, idColumn).AddComment result
        End If

NextRow:
    Next i

    MsgBox "Validation complete." & vbCrLf & vbCrLf & _
           "Valid: " & validCount & vbCrLf & _
           "Invalid: " & invalidCount & vbCrLf & _
           "Missing: " & missingCount, vbInformation, "Identifier Validation Results"
End Sub

Function IsHexString(s As String) As Boolean
    Dim j As Long
    If Len(s) < 16 Then
        IsHexString = False
        Exit Function
    End If
    For j = 1 To Len(s)
        If InStr("0123456789ABCDEFabcdef", Mid(s, j, 1)) = 0 Then
            IsHexString = False
            Exit Function
        End If
    Next j
    IsHexString = True
End Function

Run the macro from Developer > Macros > ValidateIdentifiers. It will prompt you for the column number and starting row, then work through every cell. When it finishes, you get a color-coded spreadsheet (green for valid, red for invalid, yellow for missing) and a summary dialog.

Hover over any red cell to see the comment explaining exactly what is wrong: a format error tells you the structure is broken, while a checksum error means the digits are plausible but the check character does not match.

Common EIDR Issues and Fixes

These are the problems this macro catches most often in real catalogs:

• Leading or trailing spaces. Copied from emails or PDFs. The macro trims these automatically, but fix them in your source data.
• Wrong prefix. Some systems store 10.5239 or 10.5241 by mistake. Only 10.5240 is a valid EIDR prefix.
• Lowercase hex digits. Technically valid, but some delivery systems reject them. Standardize to uppercase.
• Missing check character. The ID was truncated during copy-paste. Look up the full ID in the EIDR registry at ui.eidr.org.
• Extra hyphens or spaces. Some systems insert a hyphen after the prefix (10.5240/-XXXX...). Strip it.
• Unicode dashes. Word processors replace hyphens with en-dashes or em-dashes. Replace them with standard hyphens (U+002D).

Run this macro before every platform delivery. A two-minute validation pass prevents hours of debugging failed ingestions and weeks of delayed revenue recognition.