Pull from the right of a cell until the first instance of a delimiter is reached

You can use an Excel formula to pull all of the data from the right side of a cell until the first position (from the right, the last from the left) of a delimiter is reached. So in this example, we are extracting all of the data after the last instance of a forward slash. Each cell in column A contains a different number of delimiters - holding differing total number of field entries - so using the Text to Delimiters tool won't parse out the last name (which always comes last in the cell) into a single column.

=RIGHT(A2,LEN(A2)-FIND("|",SUBSTITUTE(A2,"/","|",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))),1))

This formula can be made to work by swapping in any delimiter in place of the forward slash (/).

So the formula works first by getting a count of the length of the cell when all references to the forward slash are replaced:

LEN(A2)-LEN(SUBSTITUTE(A2,"/","")

. . . this pinpoints the location of the last delimiter so it can be replaced with a unique delimiter - the pipe | .

SUBSTITUTE(A2,"/","|",LEN(A2)-LEN(SUBSTITUTE(A2,"/","")

The FIND formula gives the location of this unique delimiter so the RIGHT formula can pull the text from this point.

=RIGHT(A2,LEN(A2)-FIND("|

See more detail son this solution here.

This last post of 2021 will be the last time that I update this blog on a nightly basis. I have posted each night with only a handful of exceptions since April 10, 2015, but I need to switch to a more reasonable schedule. While I have no shortage of new litigation support tips, I need to take a break. It takes a great deal of effort to prepare and distribute these posts. Going forward I will post at least once each week, but not necessarily any more than that. I've missed a lot of sleep over the past six years working on Litigation Support Tip of the Night, and 2022 looks like it will be one of my busiest years at work ever.

I hope this blog is helping a lot of people out there get their work done faster and shortening some late nights of your own. Litigation Support Tip of the Night will continue . . .

Counting up attachments using IF . . . THEN

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.

How to keep an image in place in Excel

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'.