by Alexander Trifuntov, 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.
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.
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:
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:
As soon as I select the delimiter that is used in my data (space), the entire column is immediately being split to separate columns:
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.
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.
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:
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 :)
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:
So, with our add-on, you just need to:
The add-on automatically inserts 2 new columns — D and E — and pastes results there, leaving the columns with numeric data intact.
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:
See? All 6 digits in column D are separated from the text in column C. The text is also put into column E.
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:
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.
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:
The add-on is called Split Date & Time and resides in the same Split group in Power Tools:
The instrument is super straightforward:
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.
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:
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:
The range you copied will be inserted but you'll see that columns have become rows and vice versa:
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:
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
