Bates number gap checker

The Excel spreadsheet posted below tonight can be used to generate a list of Bates number ranges, listing consecutively numbered ranges in a long list that has gaps, and includes ranges from different party's productions.

Enter your list of beginning and ending Bates numbers in columns A and B. Be sure they are sorted in numerical order. The formula in column C checks to see where the difference between the number (minus the Bates prefix) in column A is a different than one whole number, but it only checks the characters in each cell after the last non-numerical character appears.


The formula is designed to do this because Bates numbers always end with numbers of several digits and almost always contain prefixes with letters, hyphens, underscores, spaces, and other non-numerical characters, and can sometimes contain numbers as well such as 'ACME-2004_00099454256', or '3M 0006532'.

The formulas in columns D and F search for the position where the last non-number appears, and the formula in column C references the result of those formulas.

=LOOKUP(2,1/(ISNUMBER(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"abcdefghijklmnopqrstuvwxyz@#$%^&+ _-:|.—"))),ROW(INDIRECT("1:"&LEN(A2))))

Thanks to tigeravatar for posting this LOOKUP formula here. Note that the formula will fail if the Bates prefix ends with any character other than these: abcdefghijklmnopqrstuvwxyz@#$%^&+ _-:|.—

It can used for more than one purpose where you need to find the last occurrence of a character or characters in a cell.

The formulas in columns E and G flag where a Bates number ends with a letter suffix. In these cases the Bates ranges will have to be manually checked.

The formula in column H checks for where the Bates prefix in column A differs from the preceding Bates prefix, and enters a new Bates number where the formula in column C indicates a gap appears.


The formula in the last column enters the ending Bates number for each consecutive range. Simply filter in this column to get the complete ranges for all Bates numbers in H and I, but be sure to manually correct the very last Bates number.

Bates Gap Checker
Download XLSX • 11KB