Split data in to rows, new line

[CypherBit]

We have a spreadsheet with thousands of rows, that looks like this:

We need to (if at all possible with formulas only) separate the data (; is the separator) in such a way that the first row above ends up looking like this (obviously it's needed for all the rows, the data must be moved downwards):

I've been searching and searching, but my Excel-foo is not up to par. Can someone please help us out?

 

[JvdV]

It may not be Excel 2019, but it's possible with:

=DROP(REDUCE(0,SEQUENCE(COUNTA(A:A)),LAMBDA(x,y,VSTACK(x,LET(z,TEXTSPLIT(INDEX(C:C,y),":",";",1),q,CHOOSEROWS(A:B,y)&"",HSTACK(IFERROR(EXPAND(q,ROWS(z)),q),TAKE(z,,-1),TAKE(z,,1)))))),1)

This is for ms365 and Excel Online where the latter is free to use I believe. See if you can utilize that.

 

[RoryA]

For 2019 I think power query would be the simplest option.

 

[JvdV]

Another option available in Excel 2019 would be to load the data in PowerQuery and split column c into new columns and rows. With some restructuring you could use the below M-Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column3", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column3"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column3", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column3.1", "Column3.2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Split Column by Delimiter1",{"Column1", "Column2", "Column3.2", "Column3.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([Column3.2] <> null))
in
    #"Filtered Rows"

 

[CypherBit]

Thank you very much. While I'm on 2019, I do have access to O365 version and have just tested it and it appears to be exactly what we need. Much appreciated.