Organizing the data in Google Sheets required a lot of slicing and dicing. One of the common tasks most people want to do is splitting cells in Google Sheets into columns and rows.
A very common example is when you have the first and last name and want to split it into separate columns. Or the house address, such as house number, street name, and other details, into separate columns.
Fortunately, splitting cells into rows and columns is easy, and you can do it within minutes. In this article, I have explained the methods to split cells in Google Sheets.
PS This tutorial should take you about 10 minutes. If you want to follow along with the tutorial, just create a copy of this spreadsheet
How to Split Data Into Columns in Google Sheets
There are two methods to split data into columns. Either you can use the options menu or the SPLIT formula. Below I have explained both methods with screenshots so you can quickly follow them to split data into columns.
Split Text to Columns Using the Options Menu
Follow this method to separate columns in Google Sheets.
Step 1: Open the Google Sheet with which you want to perform this action, and select the column containing data you’d like to split.
Step 2: From the Menu bar, select Data, and click on Split Text to Columns.
Step 3: A floating button will appear on the screen with multiple options. Choose “Space” from the drop-down list.
Note: Separator type depends on your data. We are choosing “Space” because our data is separated by spaces. If you have used a comma between the two texts, you should select a comma. You can also choose a semicolon or give a custom selection.
Step 4: After selecting “Space” or the separator, the text will get split into columns.
Split Text Using the SPLIT Function
Step 1: Select the cell next to the cell text that you want to split into columns.
Step 2: Now enter the formula
=SPLIT(cell_number, “ “)
Step 3: Press Enter and the text values will split into two columns.
Step 4: Now select the split columns, and drag the box below to split text from all columns you want.
Note: The options menu method works well if you want to split multiple cells in a column, whereas the latter method is for splitting individual cells as well offers some optional parameters (split by each and remove empty text).
How to Split Cells Vertically in Google Sheets
Follow this method to split cells into rows in Google Sheets.
Step 1: Select the cells in a column that you want to split. Now click on Data and select Split Text into Columns.
Step 2: Choose Space from the floating button, and the data will split into columns.
Step 3: Right-click on the selected data and choose Cut or press a combination of CTRL + X if you are on Windows or COMMAND + X if you are on Mac.
Step 4: Right-click on the sheet where you want to split the data vertically and navigate to Paste Special. Now choose Transposed.
Step 5: The data will be split into rows automatically.
Note: To split data vertically in Google Sheets, you must first split it into columns. Then use the “transposed” function to split it into rows. Sometimes, duplicate data may be there; you can delete the original entries and keep the vertically split data if required.
As an alternative, you can also use a TRANSPOSE function
Using Delimiters for Splitting Cells in Google Sheets
There are multiple types of delimiters used to separate text, and thankfully, Google Sheets lets you split text irrespective of the delimiter you have used. Below I’ll give you a few examples of how to split cells using various delimiters.
PS. Read this article if you want to get a better understanding of delimiter-separated values
Separate Columns Using a Custom Separator
You can still split the text into columns if the cell doesn’t have commas in between the values.
Step 1: Select the cells you want to split. Click on Data and choose Split Text to Columns.
Step 2: A floating button will appear on the screen. Click on it and select Custom.
Step 3: Another dialog box will appear. In the custom separator field, enter the separator you have in the text. For example, I have used “]” between the text values.
Step 4: Once you enter the separator value, the cells will automatically split into columns.
Split Cells by Space
The process of splitting cells in Google Sheets using space is the same as the above-mentioned process. From the floating button menu, you need to select “Space.” Press Enter and the cells will be split into columns.
Split Columns with Multiple Delimiters
Splitting columns with multiple delimiters is not technically feasible. However, you can use the REGEXREPLACE function to ensure all delimiters in the column are the same. Then you can use the Options menu to split cells into columns.
Step 1: Select the cell that you want to split text from.
Step 2: Enter the formula
=REGEXREPLACE(Cell Name, "text to be replaced," text value to replace with)
Step 3: Press Enter and the cell value will be replaced automatically.
Step 4: Similarly, use this function for all the columns to replace the non-uniform value to make them the same.
Step 5: Once done, select all columns that you want to split, and use the Options menu to split the text into columns.
Real-life Example: How to Separate Addresses in Google Sheets
Step 1: Select the cells with the address value that you want to split. Click on Data and select Split Text into Columns.
Step 2: In the Custom Separator field, enter “,”. You need to add the value that separates the text in your sheet.
Step 3: Press Enter and the address will split into multiple columns.
Some Questions You May Have
What to do when the split text to columns not working?
There can be several reasons why the split text feature is not working. Ensure you have edit access to the sheet and a stable internet connection. Also, check whether you are using the right separator value. You can also try using the SPLIT function if the Options menu method is not working.
How do I split a cell in half in Google Sheets?
To split cells in half, select the column or text, click the Data menu, and choose Split Text to Columns.
How do I split the text into rows in Google Sheets?
To split the text into rows in Google Sheets, you must first split them into columns. Select the split cells, and cut/copy them. Right-click anywhere on the screen, go to Special Paste and choose Transposed. The values will split vertically into rows.
How can I split a cell by adding a diagonal line?
There are 3 ways to split cells by adding a diagonal line in Google Sheets. You can use text rotation, a drawing tool, or even a SPARKLINE function to create a 45 degrees line
I hope this detailed guide on “how to split cells in Google Sheets” has helped you split the values you want. Ensure you use the right “separator” value to make the SPLIT function work properly. Also, keep in mind the REGEXREPLACE function to change the non-uniform separator values.
If you have any issues with using the SPLIT function, you can drop your queries in the comments section.