I was able to achieve this using Power Query/Get and Transform. I did use US date convention so that it would work on my system. You can adjust that. Here is the Mcode
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Position" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByPositions({0, 2}, false), {"Column1.1", "Column1.2"}),
#"Split Column by Position1" = Table.SplitColumn(Table.TransformColumnTypes(#"Split Column by Position", {{"Column1.2", type text}}, "en-US"), "Column1.2", Splitter.SplitTextByPositions({0, 2}, false), {"Column1.2.1", "Column1.2.2"}),
#"Split Column by Position2" = Table.SplitColumn(Table.TransformColumnTypes(#"Split Column by Position1", {{"Column1.2.2", type text}}, "en-US"), "Column1.2.2", Splitter.SplitTextByPositions({0, 3}, false), {"Column1.2.2.1", "Column1.2.2.2"}),
#"Added Custom" = Table.AddColumn(#"Split Column by Position2", "Year", each if Text.StartsWith([Column1.2.2.1],"0") then "2" & [Column1.2.2.1] else "1" & [Column1.2.2.1]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Date", each [Column1.2.1] &"/" &[Column1.1] &"/" &[Year])
in
#"Added Custom1"
and I have attached a file. Note that your data needs to be in text format for this to work.If you are unfamiliar with PQ, then check out the links in my signature .
Bookmarks