We regularly get messages asking can you split cells in Google Sheets into multiple columns.
For example, maybe you have a column of cells containing first and last name and you would like to split the data into a column for first name and a column for last name.
Another common example would be you have address data in a single cell and would like to to split it into multiple columns for things like 'street name,' 'city,' 'zip code,' etc.
Thankfully it's easy to split cells in Google Sheets using either the SPLIT() Function or the Split Text tool. We will show you how to use both below.
The SPLIT() function requires you to specify the cell to split and the delimiter used inside the cell. The formula takes the cell reference as its first argument and the delimiter as the second argument. The formula takes this form:
=SPLIT(cell reference, delimiter)
Some of the most common delimiters are spaces (between first and last name), commas (sometimes used between parts of an address), or semicolons.
For our case, we want to put the first and last name in individual cells. Therefore, our delimiter is the space between the first and last name because that is where we want to split the cell. We write the formula as follows:
=SPLIT(A2," ")
The equation will automatically use the next columns if there are extra delimiters detected. You can simply copy the cell with the formula down the column:
This formula is an array formula, so the resulting cells are dynamic and will change when the original data is edited. This can be an advantage or a disadvantage.
On one hand, it can be an advantage because editing the original data will automatically update the formula results. Also, you can easily drag the formula down to apply it to new data rows. On the other hand, sometimes you just need the result of the formula, but they are not static, which can be a problem. However, you can solve this problem by simply following the steps here to convert formula results to static values. Another solution to prevent dynamic result values is using the second method below.
Below are the steps to split the cell into multiple columns using Split Text to Columns button:
For this example, we will use the same example of splitting names into different Google Sheets cells. After selecting all of the cells, we chose the button from the Data menu and then chose the 'Space' option as our separator.
The tool splits the column into two, just as the SPLIT function does:
You might have noticed, however, that the original column was replaced by the first column of the split text. If you need to keep the original cells, using the SPLIT() function is a better option, or you could create backup versions before using the tool. Also, you cannot easily apply this formatting to new cells below the original data as easily as you can drag down a Google Sheets formula to new rows.
You can separate the text to more than two separate columns, depending on how the string and separators are configured!
One method would be to use the Split Text to Columns twice, once to split by one delimiter and then split the cells again by the next delimiter.
In this example, we set the separator as a single space and split the information into five columns. This can be done with either of the methods above.
Regardless of the method you choose, what matters the most is that you correctly choose the separator to split the columns!
One important thing to note when you want to split data into separate columns is that the new columns in Google Sheets will replace any existing data without warning. Make sure that you have cleared out the cells that will contain the split cells. This is especially important if your split data will take up three, four, or even five more columns in your sheets.
If you enjoyed this article, you might also like our article on how to sort by last name in Google Sheets or our article on how to use the Split function in Google Sheets.
If you want to learn how to set up Google Sheets email trigger, we also suggest checking out our detailed guide.
