Split text to columns 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 column into different columns using any character or a string as a delimiter, or divide text at the selected places. The Text group also features these tools.

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

  • 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.
  • Choose to Split values by capital letter to separate data at each occurrence of the letter written in the upper case.

Two additional checkboxes will contribute to the most accurate splitting:

  • 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.
  • The tool will also replace your original column with the split result right away if you tick the box Replace source data with the first result column.

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.

Ask a question (posted publicly)

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.