Split a Cell into Two or More Columns in Excel & Google Sheets
In this tutorial, you will learn how to split a cell’s content into two or more columns in Excel.
A common task in Excel is to split the contents of one cell into multiple cells. This is often necessary when data are exported from some other software to Excel and need to be sorted out before they can be used. This can be done using Text to Columns, either with a delimiter – like a space, comma, tab, etc. – or by the width of each part of the text.
Text to Columns
Using a Delimiter
Perhaps the most frequent requirement in Excel is to split full names into first and last names. Say you have the list of names pictured below in Column B and want to split it into first name (Column C) and last name (Column D). The obvious delimiter here is the space between first and last name.
1. Select a range of cells with data you want to split. In this case, it’s B2:B8.
2. In the Ribbon, go to Data > Text to Columns.
3. The wizard for converting text to columns will appear. In the first step, set the file type to Delimited (this is selected by default) and click Next.
4. In the second step, select a delimiter, which is a single space in this case. When you select the delimiter, a line appears in the Data preview to show how the data will be split.
You could also choose tab, semicolon, comma, or any other character you define as a delimiter.
5. In the final step, define the column data format. By default, new columns have a general format, but you can set them to text, date, or any other format you need (Advanced).
Select the columns (hold CTRL and, in Data preview, click on the headers of the columns whose format you want to set). Under Column data format, select Text. You also need to define the destination where the columns will be filled. In this case, the destination is cell C2.
The result is that Columns C and D are populated with split first names and last names.
Fixed Width
Another option when splitting text into columns is to use Fixed Width. When you use Fixed Width, Text to Columns splits the text at a certain number of characters rather than at a delimiter. A good example of this method is splitting ZIP code and city name, since ZIP codes are always five digits long. Say you have ZIP code and city name in Column B and you want to split them into Columns C and D.
1. To split data, first select a range of cells with data you want to split. In this case this is B2:B9.
2. In the Ribbon, go to Data > Text to Columns.
3. Again, the Text to Columns Wizard will appear. In the first step, set the file type to Fixed width and click Next.
4. In the second step, set a break line in the preview data by clicking between ZIP code and city name.
You could double-click on the break line to remove it or select and drag to move it to a different position.
5. In the final step, define the column data format. By default, new columns have a general format. (You can change that as described in the previous topic.) You also need to define the destination where the columns will be filled. In this case, the destination is cell C2.
The final result is ZIP codes and city names split into Columns C and D.
Text to Columns in Google Sheets
Splitting text into columns works a bit differently in Google Sheets. The main difference is that you can’t set the destination cell, so the split data will overwrite the initial data. Also, Google Sheets automatically detects a separator, but you can also set it as you want. The steps for splitting text cells in Google Sheets are as follows.
1. Select the range of cells you want to split (B2:B9).
2. Go to Data > Split text to columns.
As a result, you get data split into two columns, starting from cell B2. (The original column is overwritten.)
Note that, if you click on the Separator drop down in the bottom right corner, you can choose a delimiter (comma, semicolon, period, space, or custom). As you can see in the picture above, Google Sheets automatically recognizes that the column separator is a space and split the text based on that.