How do you separate comma separated values in different columns while maintaining values in the rest of the row in Google Sheets?

user_12058322 0
Viewed 291

How do you adjust comma separated values in such a way that the value separated with commas is separated and that a new row is created for this value and that the other values are the same as in the row from which the value comes? That would look like this:

From this..

enter image description here

..to this.

enter image description here

I'm actually looking for an answer that doesn't use google script when possible and without using gigantic long and complex formulas. The use of a pivot table within Google sheets may be used, but is also not my preference. But if it's not possible to use only formulas then I'm open to other answers as well.

I've had this question for over a year and I can't find serious answers online after a few hours of searching. There will be answers using a google script, but that doesn't really fall within the scope of my question. I am willing to adjust or rephrase my question if the current question remains unanswered.

I myself have no idea how to answer the question and the attempts I have made are not to be taken seriously.

google-sheets-formula google-sheets csv
1 Answers

Draggable Formula solution

Let's see if I can get this ball rolling.

At a Glance

This solution is unfortunately unstable, as it relies on the Flatten undocumented function (turn any range into a column array), and requires two formulas to work. While I'm sure that you can do the same thing without Flatten(), this at least saves us some typing, as we rely on it heavily. Without flatten, we can achieve the same with TRANSPOSE(SPLIT(TEXTJOIN(...)), which is not nearly as elegant.

The core formula, while it does have a linear growth factor and can get messy with more columns, does have an easy pattern to follow for the setup. It can also be dragged, which is the next best thing to a single ArrayFormula.

Stage 1: Serialize Rows

As you might have expected, we're going to use some string serialization tricks to get what we want. Here's the core formula:

=TEXTJOIN(",",,
ArrayFormula(
    Flatten(Flatten(Flatten(Flatten(Flatten(
      SPLIT(A1,",")&",")&
      SPLIT(B1,",")&",")&
      SPLIT(C1,",")&",")&
      SPLIT(D1,",")&",")&
      SPLIT(E1,",")&";")
))&","

As you can see, it accounts for any commas inside each cell in the row. To add more columns, simply add another Flatten( and add your column to the list. Just make sure that the last one uses a ; and not a ,.

We take advantage of the fact that, in general, when ArrayFormula is applied to a column vector and a row vector, we can do an operation on every permutation of the two mixed together.

Examples:

  • =ArrayFormula({0;1}&{2,3}) is equivalent to ={"02","12";"03","13"}
  • =ArrayFormula(SEQUENCE(10)*SEQUENCE(1,10)) gives us a 10x10 multiplication table.

In our case, we use this to generate every possible permutation of rows based on the commas in each cell, serializes the row into a CSV string, ending each in a semicolon, then joins all the rows into one long string. The extra "," is so we can concatenate multiple tables together in the next stage.

When you're set up with the proper number of columns, drag this down to the height of the table. (Note: If some of your values can be blank, you also have to do some error checking around each SPLIT.)

Stage 2: Deserialize

This formula is considerably simpler. (Assuming serialization data is in column F.)

=ArrayFormula(
  SPLIT(
    TRANSPOSE(
      SPLIT(
        JOIN(,F:F),
        ";,",
      )
    ),
    ","
  )
)

First, glue all the strings together using JOIN. Since we know that each row ends with a ";,", we split on that to get our rows. After that, we can split each row up into cells by splitting on ",", resulting in our table.

Conclusion

  • It's not a single ArrayFormula, sure, but neither of these formulas is really all that complex, which is nice. ArrayFormulas can get messy and confusing quickly.
  • We've managed to avoid scripting too, which is a plus.
  • You can also hide the serialization column if you find it unsightly!

Hope this was at least somewhat useful.

user_42943990
Related