Extract date from a string

%3CLINGO-SUB%20id%3D%22lingo-sub-1436247%22%20slang%3D%22en-US%22%3EExtract%20date%20from%20a%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1436247%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20there%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20message%20i%20copy%20every%20day%20and%20it%20has%20dates%20in%20them%20as%20following%20format%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EWed%20Jun%203rd%2C%202020%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EHow%20can%20i%20come%20up%20with%20an%20equation%20to%20change%20this%20into%20a%20date%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1436247%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1436359%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20date%20from%20a%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1436359%22%20slang%3D%22en-US%22%3E%3CP%3EI%20managed%20to%20make%20it%20work%20with%20LEFT%20and%20RIGHT%20equations%20then%20DATEVALUE%20then%20DATE%20to%20combine%20all%20of%20them%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20theres%20any%20easier%20way%3F%20because%20this%20depends%20on%20the%20format%20never%20changing%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1436489%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20date%20from%20a%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1436489%22%20slang%3D%22en-US%22%3ECan%20you%20give%20any%20further%20context%20on%20what%20you're%20using%20this%20for%20or%20doing%20with%20the%20message%3F%3CBR%20%2F%3E%3CBR%20%2F%3EAre%20you%20copying%20from%20one%20location%20a%20date%20that's%20given%20to%20you%20in%20that%20format%20and%20then%20pasting%20into%20a%20spreadsheet%2C%20if%20so%20what%20do%20you%20do%20with%20it%20next%2Ffor%20what%20reference%3F%3CBR%20%2F%3E%3CBR%20%2F%3EOr%20are%20you%20trying%20to%20have%20a%20field%20in%20excel%20state%20the%20current%20date%20information%20in%20that%20context%20for%20something%20else%20to%20reference%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1437191%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20date%20from%20a%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1437191%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F626570%22%20target%3D%22_blank%22%3E%40Towaiji2350%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20you%20should%20at%20least%20consider%20using%20Power%20Query%20to%20import%20the%20data.%26nbsp%3B%20The%20following%20splits%20the%20text%20and%20rebuilds%20the%20date%2C%20in%20this%20case%20using%20non-US%20formatting%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table2%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%23%22Split%20Column%20by%20Delimiter%22%20%3D%20Table.SplitColumn(Source%2C%20%22Dates%22%2C%20Splitter.SplitTextByDelimiter(%22%20%22%2C%20QuoteStyle.Csv)%2C%20%7B%22Weekday%22%2C%20%22Month%22%2C%20%22Day%22%2C%20%22Year%22%7D)%2C%0A%20%20%20%20%23%22Split%20Column%20by%20Character%20Transition%22%20%3D%20Table.SplitColumn(%23%22Split%20Column%20by%20Delimiter%22%2C%20%22Day%22%2C%20Splitter.SplitTextByCharacterTransition(%7B%220%22..%229%22%7D%2C%20(c)%20%3D%26gt%3B%20not%20List.Contains(%7B%220%22..%229%22%7D%2C%20c))%2C%20%7B%22Day%22%2C%20%22Suffix%22%7D)%2C%0A%20%20%20%20%23%22Added%20Custom%22%20%3D%20Table.AddColumn(%23%22Split%20Column%20by%20Character%20Transition%22%2C%20%22Date%22%2C%20each%20%5BDay%5D%26amp%3B%5BMonth%5D%26amp%3B%5BYear%5D)%2C%0A%20%20%20%20%23%22Changed%20Type%22%20%3D%20Table.TransformColumnTypes(%23%22Added%20Custom%22%2C%7B%7B%22Date%22%2C%20type%20date%7D%7D)%2C%0A%20%20%20%20%23%22Removed%20Columns%22%20%3D%20Table.RemoveColumns(%23%22Changed%20Type%22%2C%7B%22Weekday%22%2C%20%22Month%22%2C%20%22Day%22%2C%20%22Suffix%22%2C%20%22Year%22%7D)%0Ain%0A%20%20%20%20%23%22Removed%20Columns%22%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1438218%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20date%20from%20a%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1438218%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F687774%22%20target%3D%22_blank%22%3E%40DustinDoucette%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIts%20just%20text%20from%20a%20website%2C%20and%20it%20comes%20in%20the%20format%20provided%20above%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20managed%20to%20get%20it%20to%20work%20with%20this%20formula%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DDATE(RIGHT(G2%2C4)%2CMONTH(DATEVALUE(RIGHT(LEFT(G2%2C7)%2C3)%26amp%3B%221%22))%2CLEFT(RIGHT(G2%2C10)%2C2))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20i%20am%20extracting%20and%20changing%20it%20into%20date%20format%20but%20again%20this%20would%20continue%20to%20work%20only%20if%20the%20format%20stays%20the%20same%2C%20which%20I%20think%20most%20probably%20it%20would%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebut%20is%20there%20any%20other%20way%20in%20excel%20not%20VBA%20or%20MACRO%20etc..%20I%20dont%20have%20knowledge%20of%20those%20things%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1438790%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20date%20from%20a%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1438790%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F626570%22%20target%3D%22_blank%22%3E%40Towaiji2350%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPower%20Query%20is%20on%20the%20Data%20ribbon%20tab%20and%20there%20is%20a%20'get%20from%20web'%20option%20in%20which%20you%20provide%20the%20URL%20and%20then%20Excel%20reads%20the%20data%20back%20for%20editing%20before%20loading%20it%20to%20a%20Table.%26nbsp%3B%20If%20the%20source%20data%20content%20changes%20over%20time%2C%20the%20refresh%20button%20will%20fetch%20the%20new%20dataset%20from%20the%20web%2C%20edit%20it%20as%20before%2C%20and%20load%20it%20to%20the%20workbook.%26nbsp%3B%20No%20need%20for%20a%20browser%20or%20for%20any%20cut%20and%20paste%2C%20just%20'refresh'.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

5 Replies
Highlighted

I managed to make it work with LEFT and RIGHT equations then DATEVALUE then DATE to combine all of them

 

Let me know if theres any easier way? because this depends on the format never changing 

Highlighted
Can you give any further context on what you're using this for or doing with the message?

Are you copying from one location a date that's given to you in that format and then pasting into a spreadsheet, if so what do you do with it next/for what reference?

Or are you trying to have a field in excel state the current date information in that context for something else to reference?
Highlighted

@Towaiji2350 

I think you should at least consider using Power Query to import the data.  The following splits the text and rebuilds the date, in this case using non-US formatting

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Dates", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Weekday", "Month", "Day", "Year"}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Split Column by Delimiter", "Day", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Day", "Suffix"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Character Transition", "Date", each [Day]&[Month]&[Year]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Weekday", "Month", "Day", "Suffix", "Year"})
in
    #"Removed Columns"

 

Highlighted

@Peter Bartholomew @DustinDoucette 

 

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

@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'.