2

I have a Google spreadsheet with 2 columns and thousands of rows. Every line consists of a unique ID (ex: 123) for the first column, and a list of words for the second, like this: "hello","example","love"

123 | "hello","example","love"

How to make it so lists are broken down into seperate words with each one having its own row, and matched with the original ID, which in my last example would give:

123 | hello

123 | example

123 | love

I doubt it's feasible through Google Sheet itself though so any idea of how I should proceed in any other way? Thank you so much for reading!

(Sorry for the non explicit title, I had no idea how to phrase that properly in English lol)

player0
  • 124,011
  • 12
  • 67
  • 124
kpeirt
  • 107
  • 1
  • 1
  • 6

1 Answers1

4

try:

=ARRAYFORMULA(TRIM(QUERY(SUBSTITUTE(SPLIT(FLATTEN(IF(A1:A="",,A1:A&"♦"&
 SPLIT(B1:B, ","))), "♦"), """", ), "where Col2 is not null", 0)))

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    That's so freaking&insanely cool, it works! For anyone wondering, I had to switch my Google account's default language to English to make the formula work because the syntax differs across languages and even replacing the comma with semi colons, which is usually enough, didn't work. Thank you player0 for that quick and super helpful solution! <3 – kpeirt May 19 '20 at 16:44