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
- Go to Extensions > Power Tools > Start in the Google Sheets menu:
- Open the Split (or Text) group on the add-on sidebar:
- Start the Split text tool by clicking on its icon:
- 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:
- 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 theCustomoption 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 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.
- 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 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 optionTreat consecutive delimiters as one.
- The tool will also replace your original column with the split result if you tick the box Replace source data.
- Finally, decide if you want to split values from the selected range into separate 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:
- 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.
- 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.
- Once the positions are set, choose whether to split into rows or columns from the drop-down at the bottom:
