Forum Discussion

Copper Contributor

Jun 03, 2020

Extract date from a string

Hello there I have a message i copy every day and it has dates in them as following format Wed Jun 3rd, 2020 How can i come up with an equation to change this into a date? Regards

Formulas and Functions

Towaiji2350

Copper Contributor

Jun 03, 2020

PeterBartholomew1 Dustin-Doucette

Its just text from a website, and it comes in the format provided above

I managed to get it to work with this formula

=DATE(RIGHT(G2,4),MONTH(DATEVALUE(RIGHT(LEFT(G2,7),3)&"1")),LEFT(RIGHT(G2,10),2))

So i am extracting and changing it into date format but again this would continue to work only if the format stays the same, which I think most probably it would

but is there any other way in excel not VBA or MACRO etc.. I dont have knowledge of those things

PeterBartholomew1

Silver Contributor

Jun 03, 2020

Towaiji2350

Power Query is on the Data ribbon tab and there is a 'get from web' option in which you provide the URL and then Excel reads the data back for editing before loading it to a Table. If the source data content changes over time, the refresh button will fetch the new dataset from the web, edit it as before, and load it to the workbook. No need for a browser or for any cut and paste, just 'refresh'.