June 30, 2020

You can use three Excel formulas to extract a folder from a file path.

In this example we have file paths listed in column A.

1. In column B enter this formula:

=FIND("|",SUBSTITUTE(A1,"\","|",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))-1))

. . . this finds the position of the next to last slash.

2. In column C enter this formula:

=FIND("\",A1,B1+1)

. . . this finds the position of the last slash

3. In column D, the MID - middle - formula pulls the data between the positions identified in the two columns to the left:

=MID(A2,B2+1,(C2-B2)-1)

You can go one folder back further in the file path by changing the formula to get the position of the next slash:

=FIND("|",SUBSTITUTE(A2,"\","|",LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))-2))

. . . at the end the 1 is changed to a 2. You can re-use the formula from step 1, and then simply repoint the MID formula to the new range results.

June 17, 2020

Don't miss that in Excel when you want to reference a date in another column (that is formatted as a date, not as text) in a formula in another cell and display it in the correct date format, you can use the TEXT function.

In this example we see that when a reference is made to a date in a formula using a simple cell reference, we get the date converted to a number.

If instead the formula is written using the TEXT function:

="Letter dated, "&TEXT(A2,"mm/dd/yyyy")

. . . the date will be shown in the result of formula.

May 30, 2020

When adding letter suffixes to exhibits, it's common to have to go past Z, and begin a two letter sequence. AA, AB, AC, and so on. This formula will allow you to generate a list of two letter pairs running in order from A to ZZ.

=IF(G9>25,CONCATENATE(CHAR(MOD(QUOTIENT(G9-26,26),26)+65),CHAR(MOD(G9-26,26)+65)),CONCATENATE(CHAR(MOD(G9,26)+65)))

This formula can be placed anywhere on a worksheet, so long as there is a helper column next to it with a list of consecutive numbers from 0 to 701. In this example the formula points to G9 in which I have entered zero. Pulling down the formula with CTRL + D to be adjacent to the list of numbers, will generate a list of letters from A to Z, then from AA, AB, AC . . . to AZ, then from BA to BZ, and from CA all the way to ZZ.

The formula first checks to see if the first 26 letters of the alphabet have already been generated, and then generates the two letter suffixes.

Thanks to Sundar for the idea first posted here.

May 29, 2020

Don't miss that you can use the DATEVALUE function in Excel to correctly format dates entered in a column, if selecting the cells and right clicking and selecting Format Cells and picking the date format on the Number tab doesn't do the trick.

In this example, a formula has been entered in column D to create a date based in numbers listed in the columns to the left. Even though column D is formatted for dates in the MM/DD/YYYY format, and the value of the formula has been pasted into the cells. The date can be correctly formatted by clicking into a cell, but we don't want to have to do this for the entire range.

Using DATEVALUE in a column to the right (just referencing the cells with the dates) will transform the dates to the correct format - or at least those dates that have not already been converted.

May 21, 2020

On the Review tab of Excel, you will find the option to password protect a worksheet. [Note that if you choose the option to protect the workbook, this will only prevent worksheets and other structural elements of the Excel file from being changed or removed. It will not prevent the data on a worksheet from being copied and edited.} It's not uncommon to come across Excel files in document productions that have password protected worksheets. When you try to select data on the worksheet, you'll get this message:

Excel does not use strong encryption, and most passwords can be cracked using the VBA code posted here, and copied below. The macro will actually change the password to a sequence of As and Bs, and not reveal the actual password. The new password will be displayed in a dialog box. Click OK, and then the worksheet will be fully editable.

I tested this macro tonight on a workbook protected with Excel 2019 using a four-digit number, a dictionary word, a short phrase, and an eight character alphanumeric code and it cracked each one in seconds.

Sub PasswordBreaker()

'Breaks worksheet password protection.

Dim i As Integer, j As Integer, k As Integer

Dim l As Integer, m As Integer, n As Integer

Dim i1 As Integer, i2 As Integer, i3 As Integer

Dim i4 As Integer, i5 As Integer, i6 As Integer

On Error Resume Next

For i = 65 To 66: For j = 65 To 66: For k = 65 To 66

For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66

For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66

For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126

ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _

Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _

Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

If ActiveSheet.ProtectContents = False Then

MsgBox "Password is " & Chr(i) & Chr(j) & _

Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _

Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

Exit Sub

End If

Next: Next: Next: Next: Next: Next

Next: Next: Next: Next: Next: Next

End Sub

May 19, 2020

ENF Discovery has a great add-in for Excel that can help you parse out fields in Concordance .dat load files. Concordance load files use pilcrows (¶) and thorns (þ) to separate out different metadata fields. The pilcrow designates a column; thorn a quotation mark; and ® a new line.

It can be tricky to separate the fields in the .dat file into separate columns in an Excel workbook. DAT-daddy does the work for you. It installs as an add-in for Excel. A new tab will be created in the menu. DAT-daddy lets you customize the delimiters.

The numerous fields of the .dat file are effortlessly separated into new columns.

May 18, 2020

When you've created a pivot table in Excel, you can easily add two fields together and list the result in new field - one not present in your original data source.

1. When you begin the pivot, add the main field from the pivot table you will review data for and a second field which has entries to be tracked for each of the main field items, to the Row area of the field list. In this example, we want to review the groceries sent to three different cities.

2. Next in the pivot table, select a cell with one of the items you want to run a calculation for, and then go the Analyze menu, go to Fields, Items, & Sets . . . Calculated Item.

3. Give a name to a new field, and then select the field that has the items that you want to add up. In the formula box, simply list the items separated by a plus sign. You should see the field whose items are referenced in the formula in the title bar of the dialog box.

4. Finally, pull down a third field into the values area, and set it so a count of that field is generated. The new field, in this example 'Total_Fruit' will show just the sum of the formula you entered.

Naturally, a calculated item can show the result of any number of formulas.

May 14, 2020

You can use this formula to extract the nth word or string in a cell. In this format, the number of the string to be extracted is referenced in cell $B1

=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))), (B$1-1)*LEN($A2)+1, LEN($A2)))

Cell $A2 references the cell with multiple strings that is to be reviewed. The formula uses the LEN formula to calculate the position of the string, and the MID formula to extract the string.

Putting this formula in cell B2 and pulling it to the right (CTRL + R), will extract out the nth word in cell A2 which matches the number listed in the first row. So you can designate the number position of the strings you want to extract in successive cells, and pull out the needed data without revising the formula. This works well to separate the words in a person's name into different cells.

May 10, 2020

Excel's VLOOKUP formula can run frustratingly slow if you're working with large datasets. It functions by searching each row in an array to see if an exact match can be found when the common VLOOKUP(A2,D:G,4,FALSE) format is used - the FALSE designating an exact match.

The binary version of VLOOKUP using TRUE at the end can work if the data in the searched on array is sorted by the first column. it functions by first checking to see if the searched for value (in the above example A2) is above or below the middle of the search on array. Half of the values to search are ruled out immediately. A VLOOKUP binary search then keeps repeating this process, starting in the middle of one half and excluding a quarter from its review, and so on.

The trouble is that VLOOKUP with the TRUE setting will return the closest possible match - not an exact match. To overcome this limitation, use a VLOOKUP inside a IF . . . THEN formula

=IF(VLOOKUP(A3,I:K,1,TRUE)=A3,VLOOKUP(A3,I:K,3,TRUE),NA())

So instead of this incorrect result returned by a binary VLOOKUP formula:

. . . in the IF . . . THEN formula VLOOKUP first looks to see if the approximate match returns an exact match. If it does it returns that match. If not, it returns a N/A result.

This should function faster than a standard VLOOKUP 'FALSE' formula by several orders of magnitude.

May 7, 2020

The Tip of the Night for August 17, 2015, discussed using a nested INDEX formula as an alternative to VLOOKUP in Excel. Testing by Charley Kyd, discussed here, indicates using the INDEX and MATCH formulas in separate cells can get you much faster results. A nested INDEX MATCH formula only runs slightly faster than VLOOKUP, but when the INDEX and MATCH formulas are placed in separate cells, results can be obtained almost five times faster than with than with VLOOKUP.

So instead of entering the nested INDEX MATCH formula this way:

. . . enter the formulas in two columns searching for the row in the data range to be searched that the matches the unique value in the array you want a value from that range to be added to.

Then in a second column enter an INDEX formula that references the row number returned by the MATCH formula, but points to the column you want data pulled form.

Sean O'Shea has more than 15 years of experience in the litigation support field with major law firms in New York and San Francisco. He is an ACEDS Certified eDiscovery Specialist and a Relativity Certified Administrator.

The views expressed in this blog are those of the owner and do not reflect the views or opinions of the owner’s employer.

All content provided on this blog is for informational purposes only. The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site. The owner will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information.

This policy is subject to change at any time.