Columns in Google Sheets - split text to columns and convert columns to rows

I'd like to share with you a few more pieces of advice about columns in Google Sheets. If you ever needed to split the content from one column into separate ones or turn the table around so that columns become rows, this is your lucky day. Today I'm going to reveal a few quick tips on how to do that.

How to split cells to columns in Google Sheets

If cells in a column contain more than one word, you can split the content into separate columns. This lets you filter and sort data in your table easier. Let me show you a few examples.

Separate text to columns manually in a spreadsheet

I want to separate product names in my table. I select column E, go to Data > Split text to columns, and a floating pane appears at the bottom of my spreadsheet. On this pane, I choose the separator that is used between the words in my column - comma, semicolon, period, or space.

There's always an option to indicate custom separators or have Google Sheets detect them automatically:
Split text to columns in Google Sheets.

As a result, the column is cut to a few separate columns - they are added to the right of the original one.

Note. The original column is always overwritten with the first part of your data. Initial values are always removed.

Look what result I've got:
The result after splitting text to columns.

Product names are separated into 3 columns, but the datasets I had about the quantity and total were also overwritten. That's why I advise you to insert a few empty columns to the right of your original one to avoid losing the data.

Split text to columns with Power Tools for Google Sheets

Another quicker and easier way to split cells in Google Sheets can be found in the Power Tools add-on. After you install it, go to Add-ons > Power Tools > Split:
Access Power Tools from Google Sheets menu.

Find and click the Split text icon to run the tool:
Run the splitting tool.

The add-on allows you to split the data in a few ways. Let's have a look at them.

Split by character

The first option the add-on offers is to split the column at each occurrence of the delimiter. There's a great variety of separators - not only those you can use in Google Sheets but also custom symbols and even words like "or" and "not".

In case one delimiter follows the other right away, the add-on lets you choose to treat them as one.

All you need to do is to fine-tune the options and click the Split button:
Split text to columns using Power Tools.

The add-on automatically inserts 3 new columns and pastes results there, leaving the original column intact.

Split cells in Google Sheets by position

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

For instance, you have a product name and its code (6 digits in our case) written in one record. There are no delimiters whatsoever, and standard Google tools won't detach one from another. This is when Power Tools comes in handy:
Split text by position in Google Sheets with Power Tools.

As you can see, all 6 digits in column F are separated from text in column E. The text is also put into column G.

Split names

Power Tools also helps when you need to divide a full name into separate cells.

Select the column with names, go to Add-ons > Power Tools > Split > Split Names, and check the boxes according to the columns you want to get:
Use Power Tools to split names in your Google spreadsheet.

Tip. The add-on recognizes first and last names, middle names, salutations, titles, and post-nominals.

As you can see, the add-on can be of great help when it comes to splitting text. Get Power Tools today and start splitting cells in Google Sheets just 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 in Google Sheets. We have a special tool that splits 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

Do you think your table will look or present the information better if you interchange columns and rows? Then you've come to the right place.

I can think of a couple of ways how to do that without copying, pasting, or entering the data again.

  1. Use Google 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 to 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.

  2. Use the TRANSPOSE Google function.

    I put the cursor into the cell where my new table will start (A9) and entered the following formula there:


    A1:G7 is the range that is occupied by my original table. The cell with a formula became 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 the last method is that once you alter the data in your original table, the values will be adjusted in the transposed table as well. While the first method is kind of creates a "photo" of the original table in its one state.

However, both methods deliver you from copying and pasting, so feel free to use any you like most.

I truly hope that from now on each column in Google Sheets will submit to your tasks.

Happy winter holidays!

2 responses to "Columns in Google Sheets - split text to columns and convert columns to rows"

  1. Pamela Rachil says:

    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?

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 :)