Skip to Content

How to Split Text into Rows in Google Sheets

The Split tools help you categorize or manage the text from one group of selected rows or columns into multiple rows or columns.

It is possible using the characters or strings as delimiters or divide the text at the selected places.

Google Sheets may alter the format of the resulting cells following their new contents if you divide cells formatted as dates, times, currencies, etc.

Whatever the purpose is, this guide teaches amazing ways to split text into rows in Google sheets.

What are the Purposes of Splitting Text into Rows?

Helping with Employees’ List: If you have inserted the first, middle, and last names of employees and need an individual row for each value, splitting text is really helpful.

Importing and Formating the Data: If you have imported some data or value from other software tools such as Tally, which function in a comma-delimited format. You can split the imported content and convert them into a tabular form. 

Working with a SIF file or a SIF file: If you own a comma-delimited SIF file or a file created using Google Sheets for WPS, you can organize them by splitting texts.

How to Split Text into Rows in Google Sheets via Feature [Split Text to Columns and Transpose]

Google Sheets also own Split Text to Columns and Transpose tool or feature to split a text cell into multiple rows.

Import your spreadsheet into Google Sheets or make a new spreadsheet and paste it into the data you want to split.

  • Step 1: Click or select a cell containing text (Cell A1), look at the toolbar, and go to Data > Split text into columns.
  • Step 2: Click the Separator button that you have used. The separator can be a comma, semicolon, full stop, space, or custom character. With that done, text from B1 has now been split into columns A:E, based on a semicolon delimiter.
  • Step 3: Now, you can employ the Transpose Data feature to convert values from Row 1 to Column A. Begin with cell B1 (so the Cell A1 value will stay there), and transpose cells B1:E1.

Select and right-click the cells to transpose ( B1:E1), and click Copy (or use the keyboard shortcut CTRL/ Command + C).

  • Step 4:  Choose a cell from which you wish to transpose data (Cell A2), choose to Paste special, then Paste transposed.

Finally, values from cell A1 are now transposed in Column A, cells A1:A5.

You can now split multiple text cells into rows using the same methods or examples.

Split Text via Extension

  • Step 1: Run the Power Tools

Go to Extensions from the menu or toolbar and find Power Tool. If not found, install through Add-ons. Once done, run the Split Tool and Open the Split (or Text) group.

Click the corresponding icon, choose the column you want to divide, and tweak the settings to split the data.

This allows you split up a column at each occurrence of the delimiters you pick or enter:

  • Step 2: Split Data via Characters in Google Sheets

After finding the split value by characters, tick the checkboxes with the delimiters you have inserted with the value or text.

You may have space, comma, line break, and semicolon options.

If not, there may have other special characters, so employ a custom separator for the values. Choose the custom option and enter your delimiter in the field.

Now, Use the Split values by strings option to divide records into groups based on a character combination. Suppose you have entries separated by “and” in your spreadsheet; you can easily fetch them to different columns or rows.

Pro Tip: If the strings to split by vary in text cases, tick off the Match case checkbox.

Select the split values by capital letter to separate data at each occurrence of the letter typed in the upper case.

Finally, you must decide if you want to split text or values from the selected range into separate rows or columns.

  • Alternatively, Split Cells by a Given Position

If it is hard to use and distinguish a delimiter, you may have left only one option: dividing text by fixed length.

Choose the text cells you wish to split. Once done, you can witness the preview area in the add-on sidebar showcasing the first selected text. This also allows you to add break lines after any character.

Click once, and you will witness a red line before the characters that will be taken to a new column or row.

Pro Tip. Simply click on the break line again to take it out if you accidentally added one.

Select whether to split into rows or columns from the drop-down menu at the bottom once the positions have been set.

Final Thought

Splitting text or value into rows or columns helps in plenty of accounting and categorizing things on Google Spreadsheets.

Also, to perform this, you may need to learn basic sheet functionality. You can accomplish it with a single attempt and make your own tutorial.