Apr 1, 2024
Split a delimited list into two columns
Functions used: TEXTSPLIT, TEXTJOIN
Formula: =TEXTSPLIT(TEXTJOIN("~", TRUE, list), "/", "~")
Context: you have a delimted list which you want to split into two columns.
If you go row by row you can do it using TEXTSPLIT on its own, but if you want the result in a single array this won't work. Instead, you can use TEXTJOIN to combine the values into a single string, then use TEXTSPLIT on that.
Step-by-step
Evaluating TEXTJOIN with [delimiter] "~" results in this:
(Setting [ignore_empty] to TRUE will skip any empty rows in the list. Not relevant here but a useful feature).
Then applying TEXTSPLIT with [col_delimiter] "/" and [row_delimiter] "~" gives the desired result:
