Split a Cell into Two or More Columns in Excel & Google Sheets

In this article, you will learn how to split a cell’s content into two or more columns in Excel.

 

split text into columns2

 

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.

 

split text initial data2

 

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.

 

data text into columns2

 

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.

 

text to column step 1

 

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.

 

text to column step 2

 

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.

 

text to column step 3a

 

The result is that Columns C and D are populated with split first names and last names.

 

text split into columns final2

 

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.

 

split text fixed initial data2

 

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.

 

data text into columns fixed2

 

3. Again, the Text to Columns Wizard will appear. In the first step, set the file type to Fixed width and click Next.

 

text to column fixed step 1

 

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.

 

text to column fixed step 2

 

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.

 

text to column fixed step 3a

 

The final result is ZIP codes and city names split into Columns C and D.

 

split text fixed final data2

 

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.

 

text to columns google sheets2

 

As a result, you get data split into two columns, starting from cell B2. (The original column is overwritten.)

 

text to columns final data google sheets2

 

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.