Sometimes a series of data needs to be split into different columns to separate and gather individual words from those data, like gathering first/last names from full names, cities from addresses, and more.Here's how to split cells in Google Sheets into different or separate columns/rows. Let's say, you got a lot of rows for full names in a single column.To identify the person a lot easier, you need to gather their first name and last name in separate columns.

Doing that for each row manually is exhausting, especially when you have hundreds or thousands of full names. You can automate the task by automatically splitting those full names into first names and last names placed at columns beside it.Do the same for addresses (to gather cities, postal code, and stuff), email addresses (to gather email services), phone number (to gather country code and stuff), and more, your job will get a lot easier. If you're new to Google Sheets or other spreadsheet tools, you might get confused as to do one.Well, it's actually easy to divide cells, and you can do it on Google Sheets for free.

How to split cells in Google Sheets with SPLIT function

There's a function for everything in a spreadsheet tool like Google Sheets, including to split a cell into different columns or rows (for transposed data).

The one you will be using is SPLIT. To do this, simply prepare columns with empty rows beside the row containing the full words of data.In this example, I prepared "First Name" and "Last Name" separately from the "Name" containing a full name. Now, on the top-left of where the split cells are going to be placed, type "=split", open a bracket followed by the cell range of where the original data is located, type a comma followed by quotation marks, and closed by a bracket. The quotation marks should contain the delimiter or separator that will tell Google Sheets which word they should split at.This example has full names with empty space as the delimiter, so I put the empty space between the quotation marks. Press Enter.

You would instantly see the split cells in separate columns for the first row.If you want it to apply for the rest of the rows, simply autofill it. To autofill in Google Sheets, select the cell you want to autofill, hover your cursor until it shows the plus icon, click and drag it to the bottom until the last row you want to fill. I would instantly split cells between empty spaces.If it gets more empty spaces between words, Google Sheets will split those words and put them automatically beside the original one sequentially. You can also do the same thing if you want to split cells into different rows (if you have transposed data) vertically.

By default, Google Sheets would split cells horizontally, so you have to copy and paste as transposed when right-clicking the spreadsheet. You can also split addresses, email addresses, and other kinds of data by using different delimiters.For instance, to split between email usernames and email services, you would have to use "@" as the delimiter. The best thing about using this function to split cells is that you can automatically make changes to the split cells by editing the source or original cells.For instance, if you edit the full name, the split first and last names beside would also change accordingly. But, you might sometimes don't want this to happen.

Instead, you want to edit the full name or the source/original cells without applying the changes to the columns beside it. You can copy and paste the split data as values to another part of your sheet or to another new sheet to make changes that way.Simply select those split cells, copy by CTRL+C or right-clicking > Copy. On another part of the sheet or a new sheet, right-click, select Paste special, and select Paste values only.

How to split cells by using the menu feature in Google Sheets

Google Sheets has another feature to lets you make your task a lot easier to get done.This one functions differently.

Instead of splitting cells by duplicating each word from the original, it will remove the original replacing them with the split words. That means you would have to backup your spreadsheet saving it as a different file or copying and pasting the original into somewhere else if you want to keep the original data safe. To do this, simply select all cells you want to split. Now, select Data.Select Split text to columns to split those cells into different columns. You would see a box with a combo box showing "Detect automatically".It instantly split cells by separator or delimiter that is detected automatically.

Google Sheets might be wrong, so click it to choose a different option. Here, you get some options of separator or delimiter to choose from, like a comma, semicolon, period, or space.You can also set a custom separator.In this example of splitting full names, I went clicking the space. It would instantly split those full names into first name and last name.

It also replaces the original data with a split one, that's why you might want to back them up first if you want to keep them. Due to its replacing the original data, I would have to replace the row header updating where the first name and last name are placed in the correct column.

---

Splitting cells in Google Sheets can be done so easily, and you can also do one for transposed data, with a requirement that you would have to transpose the data manually again once everything is split.

RELATED: How to Add a Header in Google Sheets