Extract the date from a text field and make it into a date format

I have a text field that has the following data format:

Completed|John Doe|Monday, May 17th 2021, 3:15:19 pm|

I need to extract just the date and then make that into a date format.
Need to do this in one formula. I'm trying to 'copy' this formula into a calculated Sharepoint list.

Thanks!

One example is not much to work on. Will the month always be 3 letters (e.g. Dec for December), or the full month name.

You can extract the date string using MID, starting after the first comma, and then a series of SUBSTITUTE functions around that can get rid of the ordinals (th, st, nd, rd etc.), and then DATEVALUE around that should be able to convert it into a proper date with whatever format you like (or you could use TEXT if you are exporting it to another application).

It would help if you could attach a sample Excel workbook showing a few other examples, so we can see what variations you are likely to be faced with - see the yellow banner at the top of the screen for details of how to do this.

Hope this helps.

Pete

IF your data is always structured as you showed in your single example and the date/time as the last text followed by an "pipe" (|) symbol, then give this formula a try...

Formula:

Please Login or Register  to view this content.

Note: This formula will return a serial date time so you will have to format the cell to display it with the date and time format of your choosing.