Split text to columns in Google Sheets

Power Tools provides you with various utilities to speed up most of your daily tasks. The Split group of instruments helps you bring text from one column into different columns using any character or a string as a delimiter, or divide text at the selected places.

Before you start

The Split tool cannot process dates. All cells formatted as dates will be ignored to avoid data corruption.

How to split values by characters or by position

Run the Split tool

  1. Go to Add-ons > Power Tools > Start in the Google Sheets menu:
    Start Power Tools in Google Sheets.
  2. Click on the Split icon on the add-on's sidebar:
    Click the corresponding icon to start splitting tools.
  3. Select the column you want to divide and tweak the settings to split the data.
    Tip. You can pick Add-ons > Power Tools > Split on the first step to see the tool's options right away.

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.

  • 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.

  • 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.
    Tip. Unlike standard options, the add-on can use all selected separators. 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.

Split cells by 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-in 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.
    Tip. If you put a break line by mistake, just click on it again to remove it.

Split names in Google Sheets

This tool divides multipart names from one cell to multiple cells with corresponding name units:
Split names from one cell into multiple cells.
Just check the boxes next to the name parts you want to have after splitting.

If your table has a header row, also pick the option My column has header.

Once you choose the splitting way and fine-tune the options, click Split and the add-on will create new columns with the strings divided at specified places.

Ask a question

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.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.