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

HCH

New Member
Joined
Feb 26, 2025
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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/202412:46Preparing job21/09/202415:55 Preparing for handover22/09/2024 08:43Handover complete23/09/202409:43Job in progress25/09/202412:03Job 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.
 
Hi @HCH,
Try


Power Query:
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,
 
Upvote 0
Faster version

Power Query:
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
 
Upvote 0
Power Query:
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
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top