Generate Properly-formatted Bates Numbers in a Snap

Let’s say you get the following panicked email from an attorney on your team:

Bates-email2

You understand that these are the Bates numbers for documents on the Big Bank case, but you need to convert them to the proper format in order to pull them out of Relativity all at once.  Viz.,

62 refers to BigBank_000062

7550 refers to BigBank_007550

and so on.  The tricky part of this is adding the “BigBank_” prefix while also getting the right number of padding zeroes into each number.  You certainly don’t want to type them out one at a time–right?  Excel’s TEXT function makes this easy.

First, cut and paste those numbers into Excel and put the following formula in cell B2:

Bates-formula

which generates the following result:

Bates-firstresult

Here’s how the formula works:  “BigBank_” is the prefix for each Bates number, and the TEXT formula with the “000000” argument tells Excel to convert the number in Column A to text and add the appropriate number of padding zeroes to get a total of 6 digits.  The ampersand (&) combines both pieces into a single string of text.

Next, copy the formula by finding the cross-hairs in the lower right-hand corner of cell B2 and double-clicking, or dragging them down:

Bates-copyformula

The copied formulas turn all of the raw numbers into properly-formatted Bates numbers:

Bates-allresults

That’s it!  As a housekeeping matter, it’s a good idea to get rid of the underlying formulas by copying the cells and pasting the values, like so:

First select the cells with the formulas/results, and then right click and choose Copy (or hit Ctrl+C)

Bates-copy

Then select the same range of cells and paste the values right on top of them:

Bates-psv

The cells will appear the same, but now the content of each cell is the resulting Bates number, rather than that funny formula–as reflected in the formula bar.

Bates-nomoreformula

Now these Bates numbers can be pasted into Relativity or other document review platform.  Of course, this trick will work if you have 8 numbers or 800.

Thanks for reading.  If you have a thorny Excel issue, please share it in the comments below.  To learn about hosting a CLE-approved Excel workshop at your law firm, company, public interest organization, or bar association, click here or email Ben Kusmin from Excel Esquire.

Advertisements

About excelesquire

NYC attorney and Excel enthusiast.
This entry was posted in CLE, eDiscovery, formulas, Lit Support and tagged , , , . Bookmark the permalink.

3 Responses to Generate Properly-formatted Bates Numbers in a Snap

  1. Pingback: Fast Tip Friday - Convert Doc Number List to Searchable Bates Numbers

  2. Pingback: How to Use Excel to Create Searchable Bates Numbers. | The Researching Paralegal

  3. Pingback: Excel Roundup 20150928 « Contextures Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s