Google sheets splitting text to rows

Question

I am trying to create an inventory system where I input the items of a drawer as a list separated by commas along with the drawer number and google sheets returns that list as separate cells, but with the same drawer number. This is what I have This is what I have and this is what I want it to return, ideally on a separate sheet. I split the text into the rows using =transpose(SPLIT(Sheet1!C3, ",")), but what I'm stuck on is filling the column to the left (the drawer number A1, A2,...) I did it manually but it would be a pain to do so for all of the drawers. Is there any way to do this using a formula? There won't be the same number of items in each drawer.

Here's my workbook if that's helpful. https://docs.google.com/spreadsheets/d/1ayXmwtJ1V4LB2ANgIYXscP-_KcuTWUTzMOHOYwPM_z0/edit?usp=sharing

player0 player0 · Accepted Answer · 2021-09-28T19:50:29

use:

=ARRAYFORMULA(TRIM(QUERY(SPLIT(FLATTEN(
 IF(Sheet1!C2:C="",,Sheet1!B2:B&"×"&SPLIT(Sheet1!C2:C, ","))), "×"), 
 "where Col2 is not null")))

Google sheets splitting text to rows

1 Answers