How to Split Cells in Google Sheets (Into Columns and Rows)

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

Final Thoughts 

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.