LITIGATION SUPPORT TIP OF THE NIGHT

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. The owner is not an attorney, and nothing posted on this site should be construed as legal advice. Litigation Support Tip of the Night does not provide confirmation that any e-discovery technique or conduct is compliant with legal, regulatory, contractual or ethical requirements.

Featured on the ACEDS blog.

Follow me on Twitter and see How-To Videos on my YouTube channel.

New tips for paralegals and litigation support profesionals are posted to this site each night. Click on the blog headings for better detail.

Reformatting Rows With Cut-Off Text

February 15, 2019

It very common after writing an Excel spreadsheet to a PDF or printing it out as a hard copy, to find that text in a cell has been cut-off. A cell can hold more text than it can display. The maximum height of a cell is 409.5. It's always possible to increase the amount of displayed text by increasing the width of the column (at least to a max of 255 characters), but a wider column may still not be able to show all of the text on landscaped letter or legal paper.

Here's how to deal with this problem. Note that his solution may need to be adjusted based on the number of returns used in a cell, and whether there is so much text that it must be divided into three or more rows.

1. The Tip of the Night for October 10, 2018, showed how to calculate the height of each row on a worksheet. List the heights in a column to the right of the column holding more text than can be displayed.

. . . in new module in Visual Basic. This will allow you to enter a new formula:

=findN(CHAR(10),A2,3)

. . . to find the 3rd occurrence of a return (denoted by CHAR(10)) in cell A2.

This will find the Nth occurrence of returns (denoted by CHAR(10)) in the cell holding the lengthy text. In this example we're finding how many characters into a cell the 3rd occurrence of a return occurs. If you wanted to find the 4th occurrence, we'd change the end of the formula to, "+4)".

3. Next use the LEFT formula to pull the text from the beginning of cell A2 until the 3rd occurrence of a return character: =LEFT(A2,C2)

We have put the first half of the text cell into a new cell.

4. Next minus the 3rd occurrence of the return character form the total character count of cell A2. We use the LEN formula to find the total character count.

=LEN(A2)-C2

5. In the next column to the right, enter the RIGHT formula making reference to the cell holding the value of the total character count minus the 3rd occurrence of the return character.

6. Going back to the column containing the row height, filter it for only the rows at the 409.5 maximum. (It will be necessary to have a control number in one column so you can sort the spreadsheet into its original order.) Copy both the control number and the result from the LEFT formula into the first two columns at the end of the spreadsheet, then do the same with the result from the RIGHT formula. Shade the two extracts different colors.

7. Sort the spreadsheet by the control number, and then also sort by the color of the original text, the first half extract and the second half extract