Splitting data in cell where there is no obvious delimiter available.

[HCH]

Hi all, i hope you can help me. I have a cell in Excel with data in a form which looks something like this....

16/09/2024 12:46 Preparing job 21/09/2024 15:55 Preparing for handover 22/09/2024 08:43 Handover complete 23/09/2024 09:43 Job in progress 25/09/2024 12:03 Job complete

I would like to split this data in to three columns for each date range in the string, for example:

16/09/2024

12:46

Preparing job

21/09/2024

15:55

Preparing for handover

22/09/2024

08:43

Handover complete

23/09/2024

09:43

Job in progress

25/09/2024

12:03

Job complete

I was hoping I could use the Date in the string as a delimiter, use the time in the string as a delimiter, then the remaining text until the next date, and was hopeful that Power Query might do it, and/or that AI could recognise the date and time formats and split on each occurrence, however nothing I have tried has worked so far (mind you, my PQ and AI knowledge is limited!).

I would need to do this for several thousand rows of data, and for the process to be repeatable for new data each month.

Does anyone have any ideas how I could achieve this please?

Thanks in advance for your help.

PS the response I would give would be tell those creating the data "don't collect data like this(!)" or at least that they should have some sort a delimiter in between the progress stages, but its an extract from a CRM system, and there is nothing we can do about it unfortunately.

 

[alex78]

Hi @HCH,
Try

let
A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
B = Table.Combine (List.Transform(Table.ToList(A, each Text.Split(_{0}," ")), (x)=>
    [a = Table.TransformColumns(Table.FromColumns({x}, {"x"}), {}, each try (Date.From(_, "fr_FR")) catch (x)=> x[Detail]),
     b = Table.FromRows({List.Combine(Table.Group(a, "x", {"y", each let x = [x]
        in {x{0}, x{1}, Text.Combine(List.RemoveFirstN(x,2) ," ")}} ,0, (x,y)=> Byte.From(y is date))[y])})
    ][b])),
C = let x = Table.ColumnNames(B) in Table.TransformColumnTypes(B, List.Zip({x,List.Repeat({type date, type time, type text}, List.Count(x)/3)}))
in C

Regards,

 

[alex78]

Faster version

let
A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
B = (t)=> 
    [ 
    a = Table.TransformColumns(Table.FromColumns({Text.Split(t{0}," ")}, {"x"}), {}, each try (Date.From(_, "fr_FR")) catch (x)=> x[Detail]),
    b = List.Combine(Table.Group(a, "x", {"y", each {[x]{0}, [x]{1}, Text.Combine(List.Skip([x],2) ," ")}},0, (x,y)=> Byte.From(y is date))[y]),
    c = Record.FromList(b,List.Transform(List.Positions(b), each "Data" & Text.From(_ + 1)))
    ] [c],
C = Table.FromRecords(List.Transform(Table.ToRows(A), B),null,2),
D = Table.TransformColumnTypes(C, List.Zip({Table.ColumnNames(C),List.Repeat({type date, type time, type text}, Table.ColumnCount(C)/3)}))
in D

 

[AlienSx]

let
    fx = (txt) => 
        [lst = List.Buffer(List.Transform(Text.Split(txt, " "), (x) => Value.FromText(x, "ru"))),
        gen = List.Generate(
            () => [i = 0, value = lst{0}, is_text = value is text, txt = if is_text then {value} else {}],
            (x) => x[i] < List.Count(lst), 
            (x) => 
                [
                    i = x[i] + 1, 
                    value = lst{i}, 
                    is_text = value is text, 
                    txt = if is_text then x[txt] & {value} else {}, 
                    drop = not is_text or (is_text and not (lst{i + 1}? is text))],
            (x) => if not x[is_text] then x[value] else if x[drop] then Text.Combine(x[txt], " ") else null 
        )][gen],
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
    to_list = List.Buffer(Table.ToList(Source, (x) => fx(x{0}))),
    result = Table.FromList(to_list, List.RemoveNulls, List.Max(List.Transform(to_list, List.Count)))
in
    result