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 cells first and then go Data > 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:
I pick the delimiter used in my data (space). Google Sheets immediately separates text into multiple adjacent columns by that space:
In my example above, there was some info in columns D and E: quantity and totals. But the standard tool lost them, and, as you can see, there are product names in 3 columns. Thus, if you're going to use this standard tool, be prepared. Insert a few empty columns to the right of your original data in advance to avoid losing it.
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. In your quest to master the art of splitting cells in Google Sheets, I cannot help but mention the SPLIT function. It also divides text into adjacent columns based on a chosen delimiter. The function takes the following form: 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: 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 splitting formulas: 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:
This add-on will separate text in Google Sheets in a few different ways. Let's have a look. The first way is to separate text at each occurrence of the delimiter. There are a whole lot of various separators available: Here's why this Split Text is head and shoulders above the standard Google Sheets tool: Having all these features doesn't mean that the add-on is complicated. On the contrary: it's beginner-friendly. In my case, the add-on automatically inserts 2 new columns — D and E — and puts the results there. Hence, my columns with numeric data remain 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. These text strings don't have any delimiters, so the standard Google Sheets Split text to columns option won't pull one from another. This is when Power Tools comes in handy since it knows how to cut by position:
See those digits in column D? The add-on took them from column C. But aside from numbers, there's also some text. The add-on took as well and put it into column E. And all these in just a click. This 2,5-minute video will show you the whole Split Text add-on in action: 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. 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. 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:
The tool is called Split Date & Time and it's also part of Power Tools:
The instrument is super straightforward:
So what are the drawbacks of the built-in Google Sheets tool for splitting text to columns?
SPLIT function for Google Sheets
=SPLIT(C2," ")
=ArrayFormula(SPLIT(C2:C69," "))
How to separate text in Google Sheets with the Power Tools add-on
Split cells in Google Sheets by character
Split cells in Google Sheets by position
Video: How to split cells in Google Sheets
How to separate first and last names in Google Sheets
Video: How to separate names in Google Sheets
Split date and time
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!
4 comments
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
Hello Walter,
Please visit one of the articles devoted to splitting cells in Excel: https://www.ablebits.com/office-addins-blog/search.php?search=split+cells+excel
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.