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 text 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 tool for splitting text 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.

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 column at each occurrence of the delimiter. There's a great variety of separators of your choice - 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 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 the 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 and go to Add-ons > Power Tools > Split. Open Split Names group 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 a great help when it comes to splitting the data. Get Power Tools today and start splitting text just in a couple of clicks.

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 a right place.

I can think of a couple 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:

    =TRANSPOSE(A1:G7)

    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!

You may also be interested in:

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

  1. Luis Silva-Ball says:

    Help! I'm not seeing the "separator" floating pane after clicking on the menu item. It just splits with some default separator. Any ideas why this could be happening? Thanks

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard