In Google Sheets, how to transform a list in a cell into a series of rows

You might have pasted a list (ordered or not) into a single cell of your Google Sheets.

Now you’re wondering how to transform it into a series of rows to append some data to each entry in the list. Here’s a detailed tutorial showing you how to do it in 2 (+1) easy steps.

Step 1: divide the content of your cell into multiple columns.

Let’s say that your list is located in the A1 cell of your sheet.

In the next cell, B1, paste the following formula:

=split(A1,char(10))

The purpose of the formula is to split the content of the A1 cell into multiple columns. The separator for each piece of content is a line break, expressed as char(10) (carriage return).

You could have tried to use the SPLIT TEXT TO COLUMNS function, located in the Data tab of the Google Sheets menu. But there's no option to declare a Line Break as content separator, not even via the custom field. Hence the use of the split formula. 

Now the 25 entries in the list are spread into 25 different columns, from B1 to Z1.

The next step is to transpose those 25 columns into 25 rows.

You can either do it on the same sheet or, preferably, in a second sheet.

Step 2: transpose the columns into rows

In the top left cell of your new sheet, in the A1 cell, simply paste this formula:

=TRANSPOSE(sheet1!b1:z1)

The formula automatically transposes all the cells in the range b1:z1 of the sheet1 tab into rows in your sheet2 tab.

There’s one more step if you want to remove the numbers from the list.

Step 3 (optional): remove the numbers from each row

In the B1 cell of your second sheet, simply paste this formula (and drag it down across the 25 rows to apply it to each line).

=REGEXREPLACE(A1,"\b([0-9]|[1-9][0-9])\b.","")

The formula will remove all numbers + the dot and replace them by an empty space.

You’ll end up with 25 clean rows in the B column of sheet2, ready for further processing.

How to transform a list in a single cell into multiple rows using Python (+ Google Sheets)

If you’re using Python, it’s even easier to transform your list into multiple rows.

Here’s the code you can use.

#import libraries 
import gspread
import time 


#connect to the Google Sheets API via gspread. Follow gspread tutorials to generate your creds.json file

gc = gspread.service_account(filename="creds.json")

#declare both sheets (source_sh = the sheet feat. the source list and row_sh = the second sheet, where you'll print out the separate rows )

source_sh = gc.open("Your Google Sheet Name").sheet1
row_sh = gc.open("Your Google Sheet Name").get_worksheet(1)

#declare the source_list value, located in the a1 cell

source_list = source_sh.acell("a1").value

#split the source_list into multiple lines 

entries = source_list.split('\n')

#iterate through the list of entries and generate one row per line. Use time.sleep to avoid exceeding the G Sheets API read-write rate

for entry in entries:

  #split at the dot (.) and use the second part ([1]) as the final entry transposed into a row. I'm using lstrip to remove the leading white space. 

  entry_no_numbers = entry.split(".")[1].lstrip()
  row_sh.append_row([entry_no_numbers])
  time.sleep(0.5)