How to split cells in Google Sheets

If you ever needed to split text from one cell into multiple columns in Google Sheets, this is your lucky day. Today I'm going to share a few quick tips on how to do that.

If your cells with data contain more than one word, you can split such cells into separate columns. This will let you filter and sort data in your table easier. Let me show you a few examples.

Standard tool to split text to columns in Google Sheets

Did you know that you can split cells in Google Sheets with the standard built-in tool? It's called Split text to columns. Though it won't work for complex tasks, it's useful enough to separate text in Google Sheets by one delimiter at a time. Let me show you what I mean.

I'm going to split product names from column C. I select the columns first and then go Data > Split text to columns: Google Sheets tool to split text to columns.

A floating pane appears at the bottom of my sheet. It lets me select one of the most commonly used separators: comma, semicolon, period, or space. I can also enter a custom one or have Google Sheets detect it for me automatically: Select the required separator in the standard tool.

As soon as I select the delimiter that is used in my data (space), Google Sheets immediately separates text from the entire column to multiple adjacent columns: ALT

So what are the drawbacks of the built-in Google Sheets tool for splitting text to columns?

  1. Not only does it always overwrite your original column with the first part of your data, but it also overwrites other columns with the divided units.

    In my example above, there was other info in columns D and E: quantity and totals. But it's lost now, and as you can see, there are product names in 3 columns.

    Thus, if you're going to use this standard tool, you'd better insert a few empty columns to the right of your original data to avoid losing it.

  2. Another limitation is that Google Sheets cannot split cells by multiple separators at a time. If you have something like 'Chocolate, Extra Dark' and you don't need a comma lying around, you'll have to split such cells in two steps — first by comma, then by space: Google Sheets: separate by comma first and then by space.

Luckily, there's another tool that takes care of your data and doesn't replace text without you saying so. It also splits your cells in Google Sheets by several separators in one go, even by the custom ones.

Feel free to hop right to it, or stick with me as I tell you about the function for the task.

Split cells in Google Sheets using the Power Tools add-on

In your quest to master the art of splitting cells in Google Sheets, the SPLIT function deserves to be mentioned. It also divides text into adjacent columns based on a chosen delimiter.

The function takes the following form:

SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
  • text is your cell (or cells, or simply text string) that you want to split. Required.
  • delimiter is the character by which you want to separate the text. Required.
  • split_by_each — controls whether you cut by each instance of the delimiter (TRUE) or only at the first one (FALSE). This one is optional and is treated as TRUE if you omit it.
  • remove_empty_text is also optional. It determines whether to include or exclude empty values in the result. They may occur if there are consecutive delimiters in a cell. So basically this one is all about whether to treat consecutive delimiters as one (TRUE, the default one) or no (FALSE).

Let me take the same column with product names and split them into separate columns using the SPLIT function. Here's what I enter to D2:

=SPLIT(C2," ") Google Sheets SPLIT function.

I also copy the formula down column D to do the same for each row. The function separates my product names by each space and places units to adjacent cells.

Note. Obviously, to use the function you need to have a few extra columns at hand.

Tip. Use SPLIT in conjunction with other functions like INDEX or ARRAYFORMULA for more advanced data manipulation:

=ArrayFormula(SPLIT(C2:C69," ")) Separate text in the whole Google Sheets column at once by incorporating ARRAYFORMULA.

How to separate text in Google Sheets with the Power Tools add-on

This one is the quickest and the most convenient way to split cells in Google Sheets. It's called Split text and you will find it in the Power Tools add-on. You can get it for free from the Google Workspace Marketplace: Run the Split text tool from Power Tools.

This add-on will separate text in Google Sheets in a few different ways. Let's have a look.

Split cells in Google Sheets by character

The first way is to separate text at each occurrence of the delimiter. There are a whole lot of various separators available:

  • The same that appear in Google Sheets: space, line break, comma, semicolon.
  • Custom symbols for you to enter.
  • Text strings & conjunctions like and, or, not, etc. — you're running the show.
  • And even capital letters — whew! :)

Here's why this Split Text is head and shoulders above the standard Google Sheets tool:

  • In case one delimiter follows the other right away, the add-on 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 utility 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.

  1. You just select those Google Sheets cells you need to split.
  2. Set up the characters to separate by.
  3. Adjust the settings at the bottom (it's just 2 checkboxes).
  4. And click the Split button:
Separate text in Google Sheets by comma and space using Power Tools.

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

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. There are no delimiters whatsoever, so the standard Google Sheets Split text to columns tool won't separate one from another.

This is when Power Tools comes in handy since it knows how to cut by position: Split cells in Google Sheets by position using Power Tools.

As you can see, all 6 digits in column D are now cut from the text in column C. The text is also put into column E. And all in just a click.

Video: How to split cells in Google Sheets

This 2,5-minute video will show you the whole Split Text add-on in action:

How to separate 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, recognizes middle names and lots of salutations, titles, and post-nominals.

  1. Select the column with names and go to Split Names this time: Split names add-on in Power Tools.
  2. And check the boxes according to the columns you want to get: How to separate names in Google Sheets.

Video: How to separate names in Google Sheets

Watch this 2-minute video to see how Power Tools separates first and last names, recognizes middle names and lots of salutations, titles, and post-nominals.

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, like this: A column with Date time data.

The tool is called Split Date & Time and it's also part of Power Tools: Split Date & Time in the Split group of Power Tools.

The instrument is super straightforward:

  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.
Separate date/time units.

As you can see, Power Tools is a must-have when it comes to splitting text to columns in Google Sheets. You can test its full capabilities for free and without registration simply by installing it from the Google Marketplace.

Have you had any difficulties separating text in Google Sheets? Share your case in the comments!

Table of contents

4 comments

  1. I have an EXCEL sheet with over 10,000 rows. In one particular column there are groups, a cell with a text/number string in bold face followed by several cells with currency amounts (regular typeface). The row with the bold face has only the one non-blank cell. The pattern repeats itself except that the regular typeface cells vary in number. I would like to split the column with the second column containing the bold text (name only) repeated next to the currency amounts in its group.

    Tom ($17)
    $6
    $4
    $7
    Fred ($1)
    $1

    $6 Tom
    $4 Tom
    $7 Tom
    $1 Fred

  2. The Transpose function was VERY helpful! However, after I got all the email addresses into one column, when I tried to delete the row with the emails, it deleted the content of the entire sheet. How do I delete the row with the original data without deleting the data in the new column?

    • Hello Pamela,

      did you try to delete the first row that the function returned? If so, you simply deleted the formula itself.
      Anyway, your formula fetches whatever lies in the source range. To disconnect the ranges, you should convert your formula to values. We have a special tool for the task that you can trial for 30 days for free, please read more here: Convert formulas to values.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)