Tonight's tip explains how to use the IF . . . THEN formula to add a new document number each time an attachment is referenced in a file name column. If we being with data like this:

. . . and we want to add a formula in column D which will lead to this result:


We can enter this formula in cell D2 and pull it down using CTRL + D


=IF(LEFT(C2,9)="file name",LEFT(C1,15)&".0001"&".pdf",C2)

This will only work if the attachments are consistently identified in column C with a file name beginning with 'File name', and you have one attachment for each parent document.


If you have more than one attachment, add a helper column in column E to get a count of the number of attachments for each document. In cell E2 enter:


=IF(LEFT(C2,9)<>"file name",0,E1+1)

This IF . . . THEN formula checks to see if the first 9 characters in cell C2 are not equal (<>) to 'file name', and returns 0 if they are not, marking each parent document. If there is an attachment referenced in cell C2, it instead adds 1 to the value already entered in cell E1. Assuming that the worksheet has the metadata sorted so that each parent document appears before each of the child documents associated with it, this will tell us which number each document is in the family.


In column D we can then enter this updated formula:

=IF(LEFT(C2,9)="file name",LEFT(D1,15)&".000"&E2&".pdf",C2)

. . . which will give us these results:


The IF . . . THEN formula looks for instances where the adjacent cell begins with 'file name', and if it does enters the first 15 characters from the cell above (the parent file name minus the file extension), three leading zeroes, and the attachment number referenced in column E. If column C indicates that it's a parent and not an attachment, the formula simply enters the value in cell C2.

The IF . . . THEN formula looks for instances where the adjacent cell begins with 'file name', and if it does enters the first 15 characters from the cell above (the parent file name minus the file extension), three leading zeroes, and the attachment number referenced in column E. If column C indicates that it's a parent and not an attachment, the formula simply enters the value in cell C2.


12 views0 comments

A regular expression search for a string between X and Y numbers can be structured by following the character search by two numbers in curley brackets separated by a comma. So this search:


\r\n[A-Za-z]{2,3}\r\n


. . . will find any instances of a word between 2 and 3 letters which is listed on one line.











5 views0 comments

After inserting an image inside a Excel cell you will have probably noticed that it can become easily misplaced when a column is resized, sorted or filtered.


You can solve this problem by right-clicking on the graphic and selecting . . . Size and Properties . . . and then in the Properties drop down menu choosing the radio button for 'Move and size with cells'.





13 views0 comments

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.