Just wonder if there is a single (perhaps complex) formula that transforms a row of cells into a matrix of cells (defined by width)?

I suppose I can do it with App Script however I think a single formula would be a more elegant answer. Hope it can be done by simple combo of google sheets built-in formula.

CodePudding user response：

Here's another way to do this.

=ARRAYFORMULA(LAMBDA(width,IFNA(HLOOKUP(SEQUENCE(CEILING(COLUMNS(B1:P1)/width),width),{SEQUENCE(1,COLUMNS(B1:P1));B1:P1},2,0)))(5))

It essentially creates a lookup table with the values in B1:P1 and their position and it uses that information to fill a nxm grid.

See