I have data like the following:

CategoryA SubA1 SubA2
CategoryC SubC1

I would like to avoid using scripts and hopefully only use one (albeit complex) formula to get an array like the following:

{"CategoryA", "CateogryA-SubA1", "CategoryA-SubA2", "CategoryB", "CategoryC", "CategoryC-SubC1", ...}

So far I have been able to get various pieces of this, but I don't know how to combine it all together. For example I can get {"CategoryA-SubA1", "CategoryA-SubA2"} with ARRAYFORMULA(CONCAT(CONCAT(A1, "-"), FILTER(B1:1, NOT(B1:1 = "")))). I have also gotten this:



The difficulty seems to come from dealing with a 2D array. I want to do a particular operation to each row, which involves doing a particular operation to each column in that row.

  • I don't think this can be done without hardcoding the maximum number of columns in the formula. Also, is the order of entries in the result required to be as shown (row by row)?
    – user135384
    Commented Dec 14, 2016 at 23:07
  • @zaq, ordering doesn't matter. I was thinking I would sort the result anyway. But yeah, I'm afraid I can't do this as elegantly as I would like. Commented Dec 14, 2016 at 23:37

2 Answers 2


I think any formula will be limited by the number of columns or rows that it can handle. Here is one for 4 columns, A-D:

=iferror({filter(A1:A,len(A1:A)); filter(A1:A&"-"&B1:B, len(B1:B)); filter(A1:A&"-"&C1:C, len(C1:C)); filter(A1:A&"-"&D1:D, len(D1:D))}) 

It removes empty cells from each column, and prepends the content of A to the columns other than A. The iferror wrapper suppresses #N/A, which are returned when a filter has empty output. The output is a column; if you prefer a row, use transpose, and if you want a single-cell output, use join.

  • There is a simpler way to get the concatenation of categories and subcategories: IF(LEN(A1:C3),{A1:A3,A1:A3&"-"&B1:C3}&",",). This should work with open references too. Commented Mar 21, 2017 at 7:27

Given (sample input)

CategoryA   SubA1   SubA2
CategoryC   SubC1   

Then (expected result)

{"CategoryA", "CateogryA-SubA1", "CategoryA-SubA2", "CategoryB", "CategoryC", "CategoryC-SubC1", ...}

Assuming that the real categories and real subcateries doesn't include blank spaces, the following formula return the desired result

      ", ",

To adapt it to large input array, replace:

  • A1:C3 by the reference for the input array.
  • A1:A3 by the reference for the main categories array.
  • B1:C3 by the reference for the subcategories array.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.