top of page

Excel Visual Basic Macro to Collect Legal Citations

There are several different plug-ins for Word which will automatically generate a Table of Authorities for you. But if you are just looking to get the cites in a brief so you can plug them into the Lexis Get & Print, you may be frustrated at having to parse out the the reporter citatoins from the case names and the venues and years of decision. Here's an easier way.

1. Starting with a Word document find and replace all of the tabs with three blank spaces. Just press CTRL + H enter ^t in the Find box and three spaces in the Replace box.

2. Now paste the entire text of the document in the first column of an Excel spreadsheet.

3. Press ALT + F11 and right click on the module icon and insert a new module.

4. Go to Tools . . . References . . . and check off Microsoft VBScript Regular Expressions 5.5.

5. Paste the VBA code posted below into the workspace. See this site which provides an explanation on how to run Regex searches in VB. and this site on how to structure the searches. The code I have edited is expanded to find most federal court reporters and cites to the United States Code.

6. In column B enter =findCites(A2) and pull down the formula with CTRL + D until it reaches the the last row with text in column A.

7. The cites will be collected in column B. See the screen grab below.

I will try to post an updated code soon that will account for additional state court decisions and statutes.

Function findCites(sentance) Dim regEx As New VBScript_RegExp_55.RegExp Dim matches, s regEx.Pattern = "(\d{3})\sU\.S\.\s(\d{3})|[0-9]{1,2} U\.S\.C\. §\s?\d+(\w+)?( \([0-9]{4}\))?|(\d{3})\sF\. Supp\. 2d\s(\d{3})|(\d{3})\sS\. Ct\.\s(\d{3})|(\d{3})\sF\.3d\s(\d{3})|(\d{3})\sF\.2d\s(\d{3})|(\d{3})\sF\. Supp\.\s(\d{3})|(\d{3})\sU\.S\.\s(\d{3})|[0-9]{1,2} U\.S\.C\. §\s?\d+(\w+)?( \([0-9]{4}\))?|(\d{3})\sF\. Supp\. 2d\s(\d{3})|(\d{3})\sS\. Ct\.\s(\d{3})|(\d{3})\sF\.3d\s(\d{3})|(\d{3})\sF\.2d\s(\d{3})|(\d{3})\sF\. Supp\.\s(\d{3})|(\d{3})\sU\.S\.\s(\d{2})|(\d{3})\sF\. Supp\. 2d\s(\d{2})|(\d{3})\sS\. Ct\.\s(\d{2})|(\d{3})\sF\.3d\s(\d{2})|(\d{3})\sF\.2d\s(\d{2})|(\d{3})\sF\. Supp\.\s(\d{2})|(\d{3})\sF\.R\.\D\.\s(\d{3})|(\d{3})\sF\.R\.\D\.\s(\d{2})|(\d{3})\sB\.R\.\s(\d{3})|(\d{3})\sB\.R\.\s(\d{2})|(\d{3})\sL\.Ed\. 2d\s(\d{2})|(\d{3})\sL\.Ed\. 2d\s(\d{3})|(\d{3})\sU\.S\.\s(\d{4})|(\d{3})\sF\. Supp\. 2d\s(\d{4})|(\d{3})\sS\. Ct\.\s(\d{4})|(\d{3})\sF\.3d\s(\d{4})|(\d{3})\sF\.2d\s(\d{4})" regEx.IgnoreCase = True 'True to ignore case regEx.Global = True 'True matches all occurances, False matches the first occurance s = "" If regEx.Test(sentance) Then Set matches = regEx.Execute(sentance) For Each Match In matches s = s & "" s = s & "" & Match.Value & " " s = s & Chr(10) Next findCites = s Else findCites = "" End If End Function

bottom of page