Text parsing with variable length

I'm sure I've just been staring at formulas for far too many hours and the solution is right in front of my face, but I'm just not getting this one work.

Column M:
##/##/#### | Variable Length Text-####

Example:
01/06/2014 | Daniel Trimble-4048

I need to parse out the different parts of Column M.

In Column R -- "Close Date", I'm successfully using:
=LEFT(M2,FIND(" | ",M2)-1)

...to extract the close date of the donation.

In Column S, I want to list the donor name--which is all of the text after " | ", and before the "-".

I don't need anything after the hyphen, and fortunately in this data, no one's name has a hyphen in it.


The Close Date is working fine for the LEFT and FIND functions, but for the life of me, I can't seem to get MID to work for the variable-length text. The text will always start in the same position -- 14, as the date and delimiter are standardized. And the last 5 characters of the text are not variable in length, so they can be cut out completely.

How do I use MID to extract everything starting at position 14, and stopping 5 characters short of the end of the text?


Thanks,
dt

01/15/2013 | Larry, Curly & Moe LLP-1234
need to extract Larry, Curly & Moe LLP

10/27/2003 | Abraham Lincoln-1965
need to extract Abraham Lincoln

02/14/2014 | Bond James Bond-0777
need to extract Bond James Bond

The MID start will always be position 14, and I need the text from 14 to whatever the end of the text string is, minus the last 5 characters.

Originally Posted by Tony Valko

Try this...

M2 = 01/06/2014 | Daniel Trimble-4048

R2: =LEFT(M2,FIND(" | ",M2)-1)

This formula in S2:

=SUBSTITUTE(LEFT(M2,FIND("-",M2)-1),R2&" | ","")

That's netting me a circular warning and a value of 0.

Never mind...minor typo. I got it.

Can you explain a little bit about what's going on here? I'm not familiar with substitute and wouldn't have thought to try this.

Originally Posted by AlKey

Are you asking me or Tony?

Sorry, I was asking Tony. But honestly either one of you would be fine as you both used SUBSTITUTE to accomplish this--something I wouldn't have thought of and am not familiar with.

Originally Posted by Tony Valko

=SUBSTITUTE(LEFT(M2,FIND("-",M2)-1),R2&" | ","")

LEFT(M2,FIND("-",M2)-1) returns 01/06/2014 | Daniel Trimble

The formula you already have in R2 returns 01/06/2014

We use the SUBSTITUTE function to "virtually" replace "01/06/2014 | " with nothing:

=SUBSTITUTE("01/06/2014 | Daniel Trimble","01/06/2014 | ","")

In English:

In this string: 01/06/2014 | Daniel Trimble

Replace this portion: 01/06/2014 |

With this: "" (nothing)

Intriguing approach. I can see this being very useful in the future. Thanks very much for the explanation.