How to extract a date from text

So what if we were extracting data from a online platform into Excel, and the extraction function didn't leave the dates formatted as a date, but as a text string? What I mean is, for example, like this:

Wednesday, 5 March 2025, 7:37 PM

But that we need is 5/03/2025. The long text string can't be used for calculations due to it effectively being a sentence: Excel doesn't recognise it. What we needed was for this date to be a normal short form date, so we could check when the entry had been entered against another date column.

I was worried because sometimes the date might be 23:55 PM or sometimes as shown... and any differences in between. And sometimes Saturday, and sometimes Friday, which means that the lengths at the beginning and the end of the text string is different. 

And because I want to extract the data many times, I needed formulae. Who wants to have to tinker each time an extract is run? So it was with a small dollop of dread that I went looking. But amazingly, I found just the formula I needed, using Excel's mid, find and len functions, thanks so Shuvo (2024). Going from the left, I got Excel to find the first comma and space from the left, then to snip out the section between that comma space and the next one using the following formula:

=LEFT(MID(F3,FIND(", ",F3)+1,LEN(F3)),FIND(",",MID(F3,FIND(", ",F3)+1,LEN(F3)))-1)

Which returned me:

_5 March 2025

But I wasn't quite finished yet. Did you notice that underscore? That wasn't a mistake: somehow I had inherited an initial blank space in the product of the formula. Try as I might, I couldn't get that space to disappear in the main formula. 

I am sure there is a way, but instead I found a fast and dirty way. I did a two step process where I then trimmed the product of the "=Left" formula in the next cell along, using "TRIM(H3)" (Sultana, 2024). The result was an actual date which I could then perform calculations on. Yay!

Sam

References:

Shuvo, S. A. (2024, April 16). How to Extract Text Between Two Characters in Excel: 4 Methods. https://www.exceldemy.com/excel-extract-text-between-two-characters/

Sultana, S. (2024, July 28). How to Trim Spaces in Excel (8 Methods). https://www.exceldemy.com/excel-trim-spaces/