How to split cells in Google Sheets

Two huge drawbacks of the standard Split Text to Columns tool

1. Overwrites your data. Correct, it always overwrites your original column with the first part of your split data. And it does the same with other adjacent columns (if there are any): it splits your cells directly into those columns.

   In my example above, there was some info in columns D and E: quantity and totals. But the standard tool overwrote them with split product names.

   Thus, if you're going to use this standard Split Text to Columns tool, be prepared. Insert a few empty columns to the right of your original data in advance to avoid losing it.

2. Splits by one separator at a time. Google Sheets cannot split cells by multiple separators simultaneously. If you have something like 'Chocolate, Extra Dark', you'll have to split such cells in two steps: first by comma, then by space:

How to split text by multi-character delimiter

To see how the SPLIT function works with a multi-character phrase, let's use the " and " separator as an example. The key is to set the split_by_each argument to FALSE. In this case, Google Sheets treats the entire phrase as one complete delimiter:

=SPLIT(C2, " and ", FALSE,)

Just to see the difference, check the screenshot below. If the split_by_each argument is set to TRUE, the formula treats each character of the delimiter string (e.g., "a", "n", "d", and the space " ") as a separate delimiter. As a result, it splits the text at every one of those characters, removing them in the process.

How to remove empty cells when splitting text in Google Sheets

And what happens when you use the last argument, remove_empty_text? If you set it to FALSE, the SPLIT function will create an empty cell wherever there is missing information between your delimiters.

For example, you have cells with addresses info: street number, street name, city and code, but some info is missing. To have 4 separate cells for each category in a row, with the empty ones clearly shown, you can use the formula:

=SPLIT(C2, ",",TRUE,FALSE)

For comparison, look at the screenshot below. As you can see, when the argument remove_empty_text is set to TRUE, the function simply ignores any empty text fields instead of including them in the result.

How to split cells with multiple delimiters

When your data is inconsistent and uses several different delimiters, you can add the REGEXREPLACE function. It finds all the specified delimiters and replaces them with a single, consistent one.

• text — the original text, a part of which you want to find and replace.
• regular_expression is the regular expression the function will search for. It replaces every match it finds.
• replacement is the new text you want to put in place of the found pattern.

The whole formula will be like that:

=SPLIT(REGEXREPLACE(A2,"[,;|]",","), ",")

As you can see, this formula takes text from A2 (and other text from this column as you put the corner down), standardizes any commas, semicolons, or pipes into commas, splits the text by those commas, and then stacks the result into different columns.

Split a cell into rows

When you have text in one cell that you want to break into a vertical list, the best solution is to combine the SPLIT and TRANSPOSE functions.
In this case, SPLIT splits the text by the delimiter and creates a horizontal list in separate columns. TRANSPOSE then takes that result and pivots it into a single column, with each item in its own row:

=TRANSPOSE(SPLIT(C2,", "))

Split a column into rows

If you need to split an entire column of cells into one long vertical list, you can combine SPLIT, TOCOL, and ARRAYFORMULA. In this formula, ARRAYFORMULA tells SPLIT to split the text in every cell in the range. TOCOL then takes all the split results from the different rows and stacks them into a single, continuous column:

=ARRAYFORMULA(TOCOL(SPLIT(C2:C6, ", ")))

This is ideal for cleaning up data from forms or databases where multiple entries are stored in single cells.

5.1 Split cells in Google Sheets by character

The quickest and most convenient way to split cells in Google Sheets is to use Split text, which you can find in the Power Tools add-on.
It splits your cells in Google Sheets at each occurrence of all delimiters. There are a whole lot of various separators available:

• standard ones: space, line break, comma, semicolon
• custom symbols (for you to enter)
• text strings & conjunctions like and, or, not, etc.
• even capital letters

Here's why this Split Text is better than the standard Google Sheets tool:

• In case one delimiter follows the other right away, the extension will treat them as one if you tell it. Something the standard tool from Google Sheets cannot do ;)
• You also control whether to replace your source column with the first part of the data. Another thing the standard splitting tool cannot do ;)
• And a cherry on top? You can even split to rows!

Having all these features doesn't mean that the add-on is complicated. On the contrary: it's beginner-friendly. Here's how you use it to split cells in Google Sheets:

1. Select those cells you need to split in Google Sheets.
2. Set up all characters to separate by.
3. Adjust the settings at the bottom (it's just 2 checkboxes).
4. And click the Split button:

In my case, the add-on automatically inserts 2 new columns — D and E — and puts the results there. Hence, my columns with numeric data remain intact.

5.2 Split cells in Google Sheets by position

Sometimes it may be difficult to distinguish a delimiter. Other times, you may want to cut only a certain number of characters from the main text.

Here's an example. Suppose you have a product name and its 6-digit code as one record. These text strings don't have any delimiters, so the standard Google Sheets Split text to columns option won't pull one from another.

This is when Power Tools comes in handy since it knows how to cut by position:

1. Just select the column with data to split.
2. And set the position for splitting by the example of the first cell:
3. Decide whether to split values to columns or rows.
4. And click Split.

5.3 How to split first and last names in Google Sheets

Power Tools also helps when you need to split names.

It will separate first and last names in Google Sheets, recognize middle names and lots of salutations, titles, and post-nominals.

1. Select the column with names and go to Split Names this time:
2. And check the boxes according to the columns you want to get:

5.4 Split date and time

While none of the tools above process dates, Power Tools has an ace up its sleeve. It has one more little helper that separates time units from date units if they are both written in a cell, e.g. 6/9/2020 9:00:00:

The tool is called Split Date & Time:

Here's how you split date & time in Google Sheets using this tool:

1. Select the column with Date time values.
2. Tick off those columns you want to get as a result: both date and time or only one of them to extract from the column.
3. Click Split.