Forum Discussion

idage's avatar
idage
Icon for Helper I rankHelper I
3 years ago

DATE FROM A TEXT STRING

Goodmorning community,

i need to solve a problem. I have a column containing for ex:

 

03-Jul-23 A

08-Nov-23*

03/11/2023

03/11/2023 A

03-Jul-23

and i need to extract date all in the same format.

Could you help me?

 

Thank you very much in advance.

 

Need Help

5 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    3 years ago

    Use this formula in a custom column where [Dates] is the column name

    Text.Start([Dates], 6) & Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)) (Text.Middle([Dates], 6, Text.Length([Dates]))){0}

    Sample code for testing

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDW9SrN0TUyVnBUitUB8i10/fLLgHwtCNdY39BQ38jAyBiNC1MO0w7mGZpAeVFKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dates = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Extracted Dates", each Text.Start([Dates], 6) & Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)) (Text.Middle([Dates], 6, Text.Length([Dates]))){0})
in
    #"Added Custom"
    • idage's avatar
      idage
      Icon for Helper I rankHelper I
      3 years ago

      Thank you very much for your help .

      It generally works but it give me Error when it find cells containin NULL or date in this format:

       

      03/11/2023