Breaking down lists of words

Question

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)

Answer 1

try:

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