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

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 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.
    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.
  • 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 if you tick the box Replace source data.

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.


How can i split text but not copy the formatting over as well?


Hello Gary,

Thank you for your question. Please note that our add-on works the same way as Google, i.e. when you insert a column on the right it will automatically pick up the formatting of the left column. For sure, we will keep this case as an idea for improvement.


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.