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