Forum Discussion

Towaiji2350's avatar
Towaiji2350
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
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jun 03, 2020

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"

 

  • Towaiji2350's avatar
    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's avatar
      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'.

Resources