In Step 1 of Text to Columns Wizard, leave the default file type (Delimited), and click Next.ģ. Select the text cells you want to split (B1:B3) and in the Ribbon, go to Data > Text to Columns.Ģ. First, separate values from cell B1:B3 to columns based on a delimiter. If you have data in multiple cells, like in the picture below, you can also split the text into rows in much the same way as the single-cell case above.įollow these steps to split the text in B1:B3 to columns:ġ. Select and right-click the cell where you want to start the transposed data (B2) and choose Transpose under Paste Options.įinally, values from cell B1 are now transposed in Column B, cells B1:B6. To do this, select and right-click the cells to transpose (C1:F1), then click Copy (or use the keyboard shortcut CTRL + C).Ħ. Start from cell B1 (so the current B1 value will stay there), and transpose cells C1:F1. Now use the Transpose Data feature to transpose values from Row 1 to Column B. You could also select columns individually in the Data preview and change their data types.įor the Destination, leave the initial cell to split data into columns starting from cell B1.Īs a result of this part, text from cell B1 is split into Columns B–F, at each semicolon.ĥ. Here you could also choose other types of data, like text or date. In the final step, leave the default data format (General) and click Finish. Other possible delimiters are tab, comma, space, and user-defined delimiter.Ĥ. In the Data preview, you can see how this data will be delimited. In Step 2, check Semicolon under Delimiters, and click Next. In Step 1 of the Text to Columns Wizard, leave the default file type (Delimited), and click Next.ģ. Select a text cell (B1) and in the Ribbon, go to Data > Text to Columns.Ģ.

First, separate values from cell B1 into columns based on a delimiter. Say you have the semicolon-delimited product list in the picture below and want to split the list into rows.ġ. If you have text in a single cell delimited with some character (space, tab, semicolon, etc), it can be split into multiple rows using Text to Columns and Transpose Data in Excel. In this tutorial, you will learn how to split a text cell into two or more rows in Excel and Google Sheets.

Paste list into excel rows