Suppose you get an angry letter from opposing counsel complaining about a set of redacted documents you produced, and attaching a list of the Bates ranges. You want to know how many pages of documents there are to look at. Here’s an easy way to calculate the total.
Start with the list of the documents with their beginning and ending Bates numbers.
Now use Excel’s RIGHT function to convert the Bates numbers into regular numbers that you can calculate with.
The RIGHT function grabs a certain number of characters from the end of a certain cell. The format is =RIGHT(target cell, number of characters). As always, Excel will remind you what the arguments are when you type in the function.
Here we want to grab the 6 characters at the end of each Bates number. Create two helper columns to store the results and calculate the number of pages in each document:
The formula in Column F subtracts the two numbers to get the number of pages. Don’t forget to add 1!
Here are the values returned by the functions:
Looks good! (Now do you see why you have to add 1?)
Finally, use the Sum function to add up all those pages calculated in Column F:
Voilà! This list of documents consists of 1,104 pages. Might be a long night!
Here are a few tips that can make this even easier:
- To copy the formulas to the entire range, use Edit –> Fill Series –> Autofill (or just click on the cross-hairs in the lower right corner of the first cell with the formula).
- To display the formulas instead of the values, Press Ctrl + ` (the ‘grave accent’ key, to the left of the 1 key). Use the same combination to toggle back.
In a future post I’ll explain how to do a similar problem in reverse: turn a list of numbers into properly formatted Bates numbers with the right number of padding zeroes.
Please leave a comment below or email me with any questions. Thanks for reading, and please pass it on!