Can I merge a multi-dimensional array into a single dimensional array in Google Sheets?
I'm looking to combine several columns into one, I do not care about order.
For example, I have a column with a collection of tags:
| || A |
| = || ====================== |
| 1 || Tags List |
| 2 || Loon, Diver, Gaviform |
| 3 || Shoveler, Anseriformes |
| 4 || Roc |
If I use the formula =ARRAYFORMULA(SPLIT(A2:A)) in B2, I would get the following output:
| || B | C | D |
| = || ======== | ============ | ========= |
| 1 || | | |
| 2 || Loon | Diver | Gaviform |
| 3 || Shoveler | Anseriformes | |
| 4 || Roc | | |
Instead, I'd like to collect that into a single column like:
| || B | | || B |
| = || ============ | | = || ============ |
| 1 || | | 1 || |
| 2 || Loon | | 2 || Loon |
| 3 || Diver | OR | 3 || Shoveler |
| 4 || Gaviform | | 4 || Roc |
| 5 || Shoveler | | 5 || Diver |
| 6 || Anseriformes | | 6 || Anseriformes |
| 7 || Roc | | 7 || Gaviform |
Is there a way to do this with a single formula such that I could do =OTHER_FORMULA_OR_FORMULAS(ARRAYFORMULA(SPLIT(A2:A))), given that the tag list might be any length?
I know it's possible to use an array constructor, e.g. {A2:A;B2:B,C2:C} to combine the columns, but given that I might have an untold number of tabs and a number of similar columns to do this on, I'm looking for a one size fits all formula.
The Google Sheets TOCOL function does exactly the thing I asked originally. The other answers will get you the same result, but in a different way.
=TOCOL(ARRAYFORMULA(SPLIT(A2:A4,", ")),TRUE)
