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 columns 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:
As soon as I select the delimiter that is used in my data (space), Google Sheets immediately separates text from the entire column to multiple adjacent columns:
In my example above, there was other info in columns D and E: quantity and totals. But it's lost now, and as you can see, there are product names in 3 columns. Thus, if you're going to use this standard tool, you'd better insert a few empty columns to the right of your original data 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, the SPLIT function deserves to be mentioned. 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 data manipulation: 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, 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 cut by position:
As you can see, all 6 digits in column D are now cut from the text in column C. The text is also put into column E. And all 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 cells in Google Sheets using the Power Tools add-on
=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.