Forum Discussion
idage
Helper I
Helper IDATE 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 form...
Vijay_A_Verma
Most Valuable Professional
Most Valuable ProfessionalUse 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"
idageHelper I
Helper IThank 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
- idage
- idage
- Vijay_A_Verma
Use this in a custom column
try [a=Text.From([Dates]), b=Text.Start(a, 6) & Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)) (Text.Middle(a, 6, Text.Length(a))){0}][b] otherwise null