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

5 Replies

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

  • Towaiji2350's avatar
    Towaiji2350
    Copper Contributor
    Jun 03, 2020

    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 

    • Dustin-Doucette's avatar
      Dustin-Doucette
      Brass Contributor
      Jun 03, 2020
      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?

Resources