How to convert a Splitting formula from Excel to Sheets (given that Textsplit is missing)

Question

I'm attempting to copy the function of the following Excel sheet into Google Sheets and running into a snag.

https://1drv.ms/x/s!ApTGA-odt1HWhzGjuVHPn9bJyr5G?e=ZxtUCi

It takes the list from A2 chops them up into the number of groups requested in c3 and displays them randomly in the group columns. ( Yes I want to to recalculate when a change is made to the sheet)

the issue is the following formula uses a number of features Google Sheets does not have.

=LET(group_count,C2,input_names_as_rows,TRIM(TEXTSPLIT(A2,,",")),how_many_names,ROWS(input_names_as_rows),random_names_as_rows,SORTBY(input_names_as_rows,RANDARRAY(how_many_names)),rows_per_group_count,CEILING.MATH(how_many_names/group_count,1),BYCOL(MAKEARRAY(rows_per_group_count,group_count,LAMBDA(each_row,each_column,IFERROR(INDEX(random_names_as_rows,(each_row-1)*group_count+each_column,1),""))),LAMBDA(each_col,TEXTJOIN(", ",FALSE,each_col)))) Trying to use the formula gives the error

Error Unknown function: 'TEXTSPLIT' I attempted to use split (SPLIT(A2,","), and no values returned but also no errors.

I cannot use Excel for the things I need to do and need to keep the end result to two rows beyond that I'd prefer not to use an app script if there was any other way and I need it to recaluate when a value in the sheet changes.

I included both spreadsheets and would love any help anyone could give in fixing this.

https://docs.google.com/spreadsheets/d/16IRhfR7OkaoeTc5k7LJGysmndE1zQtfz22VSlMDUaFs/edit?usp=sharing

Answer 1

The problem is not in the SPLIT itself

SPLIT in Google Sheets divides the string in columns instead of rows, so you should change it to TRANSPOSE(SPLIT(A2,","))

Also, there's no SORTBY in Sheets, but SORT. And SORT needs a second argument per column to say if it's ascending or descending. So after RANDARRAY, you should add a 1 (or a 0, it's indistinct if it's ascending or descending given it's random).

=LET(
  group_count, C2,
  input_names_as_rows, TRANSPOSE(SPLIT(A2, ",")),
  how_many_names, ROWS(input_names_as_rows),
  random_names_as_rows, SORT(input_names_as_rows, RANDARRAY(how_many_names),1),
  rows_per_group_count, CEILING.MATH(how_many_names / group_count, 1),
  BYCOL(
    MAKEARRAY(rows_per_group_count, group_count, LAMBDA(each_row, each_column, IFERROR(INDEX(random_names_as_rows, (each_row - 1) * group_count + each_column, 1), ""))),
    LAMBDA(each_col, TEXTJOIN(", ", FALSE, each_col))
  )
)