Extract date from string

I have a long list of file names in an excel spreadsheet which come in all shapes and sizes, the only consistent is that in the file is a month/year in the format of MMMM YYYY (eg December 2019) - where this exists in the file name varies.

How can I extract this date from the text? I will ultimately want the last date in the month so will use EOMonth function to get the day element of the date.

Thanks for your help

A sample like the yellow banner instructions will really help. As to do it we need to make sure to exclude other items. There may be other characters in the strings that we can search by

No worries Ali... past experience of other forum software means that users may only get a notification if they are replied to via a quote and otherwise only get notified if they chose to subscribe to the thread... clearly you've set your system up differently (or dont want the convenience of user notification).

In terms of your first point, clearly different forums use different software that function in slightly different ways... the forums I moderate on will notify someone to a "reply" if they are quoted irrespective of if they have subscribed to the thread (by default or active choice), similarly "at" username also notifies them. Clearly this isnt how this forum is set up so point noted.

On your second post... having looked through the cases I can see a handful where they have put a date in DD-MM-YY format but I will deal with them by exception -v- the thousands which follow the format of MMMM YYYY. Unfortunately this is only one of my problems in trying to consolidate the data which has been done over many years, by many people without strict controls... the rest so far I've managed to deal with via VBA, SQL or excel.

On the penultimate and final post... many thanks - will now test to ensure its working (not that I am doubting it will)

I may look at it for future activities... unfortunately this is on a work's machine and getting permission to install anything is a major fight and a couple of grand of testing unless its on a very short list of approved applications and plug ins. For reasons unknown, and I am not telling anyone, the machine had SSMS and SQL Express preinstalled and so I am at least thankful of not having to try and put all this data into an Access database to query!

Thanks again for the help.

I was just upgraded to 2013... the price of the software itself is generally irrelevant, its the cost for them testing that it doesnt introduce vulnerabilities, plays nicely with their bespoke systems (which is why until recently I was on 2010 and then only got 2013 after I pointed out I use their bespoke system on read only for my convenience and could live without it if the upgrade broke it) and then its packaged for delivery via centralised distribution.

There are times when it can be morally testing... at a former clients I needed to get the hashcodes for files for inclusion in a contract. Speaking to IT for the counterparty's preferred solution IT said its £6,000 and 6-9 months wait for this opensource/freeware application or if I could wait 2 months an alternative opensource solution was already going through the process (or actually the preferred solution didnt require installation so you could just download it and run it if you wanted without telling anyone). On the flip-side, if it turns out the software uploads all the data you run through it to some random person's server you may not want to be the one to explain why you uploaded 300,000 customers medical, bank and address details

All very frustrating but you can somewhat understand the corporate governance that demands it.

Yes, Microsoft isn't blindly trusted any more than anyone else. Even basic patches and updates to existing operating systems and Office etc all have to go through all this governance, regression testing and packaging (though generally much quicker if its for a major security vulnerability).

But again, imagine trying to explain to your 10 million customers why they cannot withdraw money from their bank or pay for their groceries using their card because IT blindly put in a service pack into all the banks windows desktops and servers overnight and it broke compatibility with some archaic hardware or software and rolling back is going to take a week. It creates a culture where anything is only done if it can be really justified and has been fully tested... hence most our people are still using Office 2010 - no one has come up with the business case to justify the project for Microsoft 365 yet. The fear creates governance, the governance creates technical papers/testing etc which in turn creates jobs (and generally well paid).

Worst of all is trying to do something collaboratively with another large corporate entity.. had a previous standoff where our IT Security bods said our file exchange mechanism had to be used and the other party will have to install appropriate FTP client and their IT Security stated that they do not consider FTP clients to be acceptable and we had to use their web interface solution but due to our firewall/proxy settings it limited it to files up to 10mb (which wasnt going to work).

I think it is one of the reasons why business users like SAAS options because once you've gone through the process of getting it approved for use a lot of the maintenance and small changes etc they can avoid the same level of governance than if it was a locally installed solution so can be more agile. Unfortunately though I have seen call centres having to use big maps with pins in them to arrange emergency repairs for customers when as SAAS solution fell due to a poorly applied config change resulting in long call waiting, wrong emergency engineers (customer said stratford so the advisor thought east london but was actually in stratford upon avon), lost data, manual rekeying etc.

My Excel is still running a VBA script so cannot use it... damned actuaries building ridiculously complex models.

Its the business case justification and comparison to other things in the backlog... £6k of IT time to provide a piece of software to one user (especially when that user is a temp) -v- using those IT guys to do some system change that increases revenue by a predicted 5%... I will look into it as it may be at a future client its already approved and so its just a simple job of going through the basic request process.

As I say, the barrier to getting new software approved simply stops people from requesting it in the first place.