See all How-To Articles

Use Text to Columns (Parse Data) in Excel & Google Sheets

In this tutorial, you will learn how to use Text to Columns to parse data in Excel and Google Sheets.

 

split a text cell to 2 or more columns

 

Split Text to Columns by a Delimiter

In Excel, you often have data exported from some other software. It might be formatted as a long string delimited by a separator (comma, semicolon, etc.). For example, see the picture below, which shows a list of products separated by semicolons in one cell (B1):

 

split text cell to multiple rows initial data

 

To view this data appropriately in Excel, use Text to Columns and split the products into separate columns.

1. Select a text cell (B1) and in the Ribbon, go to Data > Text to Columns.

 

split text to columns

 

2. In Step 1 of Text to Columns Wizard, leave the default data type (Delimited) and click Next.

 

split text to columns 2

 

3. In Step 2, check Semicolon as the delimiter and click Next.
The other possible delimiters are tab, comma, space, or another user-defined delimiter. In the Data preview, you can see how the data will be delimited with current settings.

 

split text to columns 3

 

4. In the final step, leave the default data format (General) and click Finish. (See the “Format Columns” section below for other options and more information on this step.) For the Destination, leave the initial cell (B1) to split data into columns starting from cell B1.

 

split text to columns 4

 

The result is text from cell B1 split into columns B–F, with column breaks based on semicolon delimiters.

 

split text to columns final

 

You can also use VBA code to split text into columns.

Format Columns

In the example above, column data formats were left as the (general) default. To instead format a column (for example, Column 4) as text, (1) click on the column in the data preview of Text to Columns Wizard Step 3, and (2) select Text for Column data format.

 

text to columns format column

 

Alternatively, to format all columns at once as text, (1) click on the first column, then (2) press and hold SHIFT on the keyboard and click on the last column in Data preview. (3) Select Text as the Column data format.

 

text to columns format all columns

 

Split a Text To Columns Manually (Fixed Width)

You can also position where columns will be split. This is often necessary if you don’t have a delimiter (or the same delimiter) in the text string and want to adjust that manually. Say you have the same data as in the previous example in cell B1, only now delimited with spaces.

1. To manually parse this text, select a text cell (B1) and in the Ribbon, go to Data > Text to Columns.

 

split text to columns fixed with

 

2. In Step 1 of the Wizard, select Fixed width and click Next. The Fixed width option will automatically recognize spaces in the text, and split the data at each space.

 

split text to columns fixed with 2

 

3. In Step 2, you can see the preview of data splitting based on a space as the delimiter. But what if you want to keep Keyboard and Mouse together in one column?
Drag the line between the two values and drop it on the next line (before Speakers). By dragging a line separator, you can manually adjust which text will go in which column. In the end, click Next.

 

split text to columns fixed with 3

 

4. In the last step of the Wizard, leave the default options and click Finish.

 

split text to columns fixed with 4

 

Finally, the text from B1 is split into cells B1:B4 based on spaces, with the exception of the third column, where you manually put Keyboard and Mouse together in cell D3.

 

split text to columns fixed with final

 

Split Text to Columns in Google Sheets

Google Sheets also has Text to Columns functionality, but the steps are different.

1. First, right-click a text cell (B1), and in the Menu, go to Data > Split text to columns.

 

google sheets text to columns

 

2. Click the Separator button that appears, and choose Semicolon.

 

google sheets text to columns 2

 

As a result of this step, text from B1 is now split into Columns B–F with column breaks based on semicolon delimiters.

 

google sheets text to columns 3

 

Split Text to Columns Using the SPLIT Function

You can also use the SPLIT Function in Google Sheets to split text into columns based on a delimiter. To do this, enter the formula:

 

google sheets split function

 

The SPLIT Function takes the given cell (B1) and separates it into adjacent cells based on a delimiter (in this case, a semicolon). With the SPLIT Function, a delimiter can be provided dynamically as a result of some other formula.