How to Split Delimited Text into Rows in Microsoft Excel and Google Sheets

Microsoft Excel

1. Start by selecting the cell containing the delimited text that you want to split.
2. Go to the Data tab on the Ribbon, and in the Data Tools group, choose Text to Columns(source).
3. This will open the Convert Text to Columns Wizard. Here, choose Delimited and click Next.
4. In the next window, check the box corresponding to the delimiter your text is using (for example, comma, space, semicolon, etc.), and click Next.
5. Choose the destination cell where you want your separated text to appear, and click Finish.

Google Sheets

1. Start by selecting the cell containing the delimited text that you want to split.
2. Go to the Data menu, and choose Split text to columns(source).
3. A separator drop-down box will appear underneath your selected cell. Choose the delimiter your text is using. Your text will immediately be split into separate columns.

Note: In both Microsoft Excel and Google Sheets, if you're splitting data in a cell, the result will spread across the subsequent columns, replacing any data in its path.

Now, in order to move these into rows, you can use the TRANSPOSE function. TRANSPOSE will take your separated, column-formatted data, and convert it into row-formatted data (source) for Google Sheets and source for Microsoft Excel.

1. In a new cell, type =TRANSPOSE(, and select the range of cells you want to transpose. Then close the parentheses ).
2. Press Enter (in Excel) or Ctrl + Shift + Enter (in Google Sheets), and your columns will be transformed into rows.

Summary Splitting delimited text into rows can be done in both Microsoft Excel and Google Sheets using a few simple steps. First, the text is split into columns using the Text to Columns feature in Excel or Split text to columns in Google Sheets. Then, the TRANSPOSE function is used to convert these columns into rows.