textsplit unknown dimension

[guillaume0314]

Dear Friends:

I have a dynamic array, a single row, each cell contained comma-separated values.

I need to textsplit these cells into columns in a matrix, problem, I dont know the max number of delimiter (comma) a cell can have, this can change dynamically, thus, I dont know the dimensions (rows) of the final array. Any help is appreciated on this matter.

textsplit, unknow dimension.jpg

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="FromArray_1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Column1],",")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Split([Column3],",")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Text.Split([Column4],",")),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each Table.FromColumns({[Custom],[Custom.1],[Custom.2]})),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{"Column2", "Custom.3"}),
    #"Expanded Custom.3" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom.3", {"Column1", "Column2", "Column3"}, {"Column1", "Column2.1", "Column3"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom.3",{"Column1", "Column2", "Column2.1", "Column3"})
in
    #"Reordered Columns"

[AliGW]

I have copied this to your existing thread where all the relevant information about your data resides: https://www.excelforum.com/excel-for...ing-table.html

Please don't open duplicate threads - you should know the rules by now.

[Glenn Kennedy]

Change the array-generating formula to:

=IF(A3:D3="","¦",A3:D3)

(or similar) and then use:

=SUBSTITUTE(IFERROR(DROP(REDUCE(0,F3#,LAMBDA(x,y,HSTACK(x,TEXTSPLIT(y,,",")))),,1),""),"¦","")

[AliGW]

Glenn - I have copied your response as well.