Split text to columns and rows in Google Sheets

Power Tools provides you with various utilities to speed up most of your daily tasks. The instruments from the Split group will help you bring the text from one set of selected columns or rows into several different columns/rows using any character or a string as a delimiter, or divide text at the selected places. The Text group also features these tools.

Video: How to split cells in Google Sheets

Before you start

If you split cells formatted as dates, time, currencies, etc., Google Sheets may change the format of the resulting cells according to their new contents.

How to split values by characters or by position

Run the Split tool

  1. Go to Extensions > Power Tools > Start in the Google Sheets menu:
    Start Power Tools in Google Sheets.
  2. Open the Split (or Text) group on the add-on sidebar:
    Click the corresponding icon to start splitting tools.
  3. Start the Split text tool by clicking on its icon:
    Click the corresponding icon to start splitting tools.
  4. Select the column you want to divide and tweak the settings to split the data.

Split values by character

The first group lets you split up a column at each occurrence of the delimiters you pick or enter:
Split data by characters in Google Sheets.

  1. Pick the first radio button within the group and tick off the checkboxes with the delimiters you have. You can split by each space, line break, comma, and/or semicolon.

    If you use a custom separator for the values, select the Custom option and enter your delimiter in the field.

  2. Use the Split values by strings option to separate records based on a combination of characters. For example, if you have entries separated by "and" in your spreadsheet, you can easily bring them to different columns or rows.
    Tip. If the strings to split by vary in text cases and it's important to distinguish them, tick off the Match case checkbox as well.
  3. Choose to Split values by capital letter to separate data at each occurrence of the letter written in the upper case.
  4. Two additional checkboxes will contribute to the most accurate splitting:
    • Unlike standard options, the add-on can consider all selected separators at once. If the characters you choose appear one after the other in your cells, you can avoid getting blank columns by checking the option Treat consecutive delimiters as one.
    • The tool will also replace your original column with the split result if you tick the box Replace source data.
  5. Finally, decide if you want to split values from the selected range into separate columns or rows:
    Split cells to columns or rows.

Split cells by a given position

When it is difficult to distinguish a delimiter, dividing text by fixed length is the only way to process data further. With this tool at hand, it is easy to split text into strings of the desired length:
Split cells by position in Google Sheets.

  1. Select cells with the text you want to split. Once you do this, the preview area in the add-on sidebar will show the first selected value and let you add break lines after any character.
  2. Click once and you'll see a red line before the characters that will be taken to a new column/row.
    Tip. If you put a break line by mistake, just click on it again to remove it.
  3. Once the positions are set, choose whether to split into rows or columns from the drop-down at the bottom:
    Split cells to columns or rows.



I have a large spreadsheet that I'm splitting into rows but I can only do 100 cells at a time or I get an error message, would the pay for subscription allow me to do more cells at one time?

Thank You!

Dear Matt,

Thank you for your comment. Please note that there is currently an issue on the side of Google that occurs when you are logged in under more than one account. I kindly ask you to sign out and log back in only under the account you used to grant permissions to the add-on and try to reproduce the issue.
If this doesn't help, please take a screenshot of the error message you're getting and send it to support@ablebits.com. We'll do our best to resolve the problem.


I have a sheet that has information on two separate lines within the same cell and I'm trying to split them into two different columns automatically. For example:


These are in the same cell and I'm trying to separate the name from the email. Is there a way to do this?

Thanks in advance,

Hello, after splitting the cells to multiple rows is there a way to then copy all the rest of the text to these multiple rows. For example, there are 4 names in cell A8. I want each name to be in a different row (B8, C8, D8, E8) and then all the information from cells A1-A7 to populate down from row A to row E. thank you!

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.