Extract text from cell which includes a date?

Good Morning!

I run reports daily and it would be a great to be able to separate the text and dates within the same narrative.
They are always in the same format which hopefully makes it easier;

Example date would look like the following;


Narrative
Smith John 01/01/2016 to 12/01/2016
Smith John 13/01/2016 to 21/01/2016
Monkhouse Bob 02/02/2016 to 05/02/2016
Pan Peter 14/01/2016 to 21/01/2016

In an absolute ideal world I would end up with data looking the below;

Candidate From To
Smith John 01/01/2016 12/01/2016
Smith John 13/01/2016 21/01/2016
Monkhouse Bob 02/02/2016 05/02/2016
Pan Peter 14/01/2016 21/01/2016

I've tried all sorts from with ISERRORS, LEN, FIND "/" etc. but I can't seem to crack it and now I'm beginning to wonder if it can even be done at all?

Any help would be greatly appreciated (even if it to say it can't be done and put me out my misery!)
Many, many thanks in advance!
Laura

Assuming your data starts in A1, use this (e.g. in B1) to get the name:

=LEFT(A1,FIND("/",A1)-4)

For the first date you can use this (e.g. in C1):

=--MID(A1,FIND("/",A1)-2,10)

Format the cell as a date. Use this in D1 to get the second date:

=--RIGHT(A1,10)

Again, format this as a date, then copy the formulae down as required.

Hope this helps.

Pete

Pete,

I cannot thank you enough! It works perfectly!!

Can I be really, really cheeky? Is there anyway to get a comma in-between the surname and first name once it's been stripped out of the text string?

So Monkhouse, Bob

As I said, sorry for being cheeky but if you don't ask . . .

Thanks again & kindest regards,
Laura

Okay, the first formula would be as follows:

=SUBSTITUTE(TRIM(LEFT(A1,FIND("/",A1)-4))," ",", ")

i.e. change the space into a comma-space. I've used the TRIM function to remove any double spaces.

Hope this helps.

Pete

Pete, if I could buy you a drink I would. . . .
Thank you ever so much, it works perfectly!!

I have never used the TRIM formula so will keep this mind. My issue with excel is that I don't know what I don't know even though I know it must be possible! My journey continues.

Thanks again, Pete!

You're welcome, Laura - glad to be able to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

As for the drink, well I'm in London in March ... (just joking).

Pete