How to split cells in Google Sheets to columns and convert columns to rows

Alexander Trifuntov by , updated on

If you ever needed to split text from one cell into separate columns or turn the table around so that columns become rows, this is your lucky day. Today I'm going to share a few quick tips on how to do that.

How to split cells in Google Sheets to columns

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 way for Google Sheets to split text to columns

Did you know that Google Sheets offers its own tool to split cells? It's called Split text to columns. It's useful enough to separate words by one delimiter but may seem limited for more complex tasks. Let me show you what I mean.

I'm going to split product names from my table. They are in column C, so I select it 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 spreadsheet. It lets me select one of the most commonly used separators: comma, semicolon, period, or space. I can also enter a custom separator or have Google Sheets detect one automatically:
Select the required separator in the standard tool.

As soon as I select the delimiter that is used in my data (space), the entire column is immediately being split to separate columns:
Split cells by space.

So what are the drawbacks?

  1. Not only does Google Sheets Split to columns tool always overwrite your original column with the first part of your data, but it also overwrites other columns with the split parts.

    As you can see, my product names are now in 3 columns. But there was another info in columns D and E: quantity and totals.

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

  2. Another limitation is that it 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:
    Split text to columns by comma first and then by space.

Luckily, we have just the add-on that takes care of your data and doesn't replace text without your saying so. It also splits your cells by several separators in one go, including custom ones.

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

There's one quicker and easier way to split cells in Google Sheets. It's called Split text and can be found in the Power Tools add-on:
Run the tool to split text.

Using this tool, you will be able to split cells in a few different ways. Let's have a look at them.

Tip. Watch this short demo video or feel free to read on :)

Split cells by character

The first option the add-on offers is to split cells at each occurrence of the delimiter. There's a great variety of separators — the same that appear in Google Sheets; custom symbols; conjunctions like 'and', 'or', 'not', etc; and even capital letters — whew! :)

The good things are:

  • In case one delimiter follows the other right away, the add-on will treat them as one if you tell it. Something the standard Split text to columns tool cannot do ;)
  • You also control whether to replace your source column with the first part of the split data. Another thing the standard Split text to columns cannot do ;)

So, with our add-on, you just need to:

  1. select the characters to split by
  2. adjust the settings at the bottom
  3. and click the Split button

 Split text by space and comma using Power Tools.

The add-on automatically inserts 2 new columns — D and E — and pastes 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 split by position:
Split cells in Google Sheets by position from Power Tools.

See? All 6 digits in column D are separated from the text in column C. The text is also put into column E.

Separate first and last names

Power Tools also helps when you need to split cells with full names into multiple columns.

Tip. The add-on separates first and last names, 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. Check the boxes according to the columns you want to get:
    Split cells and separate first, last names, and titles.

As you can see, Power Tools is a great assistant when it comes to splitting text. Get it from the Google store today and start splitting cells in Google Sheets in a couple of clicks.

Split date and time

While none of the tools above process dates, we couldn’t neglect this type of data. We have a special tool that separates time units from date units if they are both written in a cell, like this:
A column with data formatted as Date time.

The add-on is called Split Date & Time and resides in the same Split group in 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.

Date and time units split to different columns.

Convert columns to rows in Google Sheets — transpose

Do you think your table would look more presentable if you interchange columns and rows? Well, you've come to the right place then :)

There are two ways to convert columns to rows without copying, pasting, or entering the data again.

Use Google Sheets menu

Select the data you want to transpose (to turn rows into columns and vice versa) and copy it to the clipboard. Make sure to select headers as well.

Tip. You can copy the data by pressing Ctrl+C on your keyboard or using the corresponding option from the context menu:
Copy the data you're going to transpose.

Create a new sheet and select a leftmost cell for your future table there. Right-click that cell and choose Paste special > Paste transposed from the context menu:
Paste and transpose the copied data at once.

The range you copied will be inserted but you'll see that columns have become rows and vice versa:
See the transposed result on a new sheet.

Google Sheets TRANSPOSE function

I put the cursor into a cell where my future table will start — A9 — and enter the following formula there:

=TRANSPOSE(A1:E7)

A1:E7 is a range that is occupied by my original table. A cell with this formula becomes the leftmost cell of my new table where columns and rows have changed places:
Switch rows and columns using the TRANSPOSE function.

The main advantage of this method is that once you alter the data in your original table, the values will change in the transposed table as well.

The first method, on the other hand, kind of creates a "photo" of the original table in its one state.

No matter the way you choose, both of them deliver you from copy-pasting, so feel free to use the one you like the most.

I hope now you know a little bit more about how to split cells in Google Sheets and how to easily convert columns into rows.

Happy winter holidays!

Table of contents