Google Sheets: Split and Transpose

This Google Sheets trick helps you to extract lines of text out of a single cell and have the text in individual cells.

Rubric

Where I was doing this was using a rubric. Multiple criteria were entered into a single cell, but this made it difficult to mark each of the items in the list. Click Here if you would like to try out this sample spreadsheet.

Carriage Return

To get text on different lines within the same cell use Control Enter (Command Enter on a Mac).

Split

=Split() will split up text at a given delineator. This does assume there is something you can identify that splits up the text. In the case of my sample rubric the asterisk (*) was what I could use to split up the text.

This splits up the text horizontally but I need my text to be vertical.

Transpose

=Transpose() flips a range of cells from vertical to horizontal (or vice versa.). Type =transpose( into a blank cell and highlight the range of cells you wish to transpose.

The combination of =split() and =transpose helps me to get extract the lines of text and put them into individual cells. The problem with this is I am unable to drag the formula to the right to arrange split and transpose the entire category.

Nest the Formulas

=transpose(split(C4,”*”)) will do both actions at once.

Since the transpose separates the lines within the cell into individual cells you will only be able to do this one category at a time. Repeat the nested transpose and split formula for each category.

Trim

Another formula I needed to use was =trim() to trim the carriage return at the end of each sentence.