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

This tutorial demonstrates how to use Text to Columns to parse data in Excel and Google Sheets.

 

 

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):

 

 

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

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

 

 

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

 

 

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 would be parsed based on current settings.

 

 

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.

 

 

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

 

 

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, click on the column in the data preview of Text to Columns Wizard Step 3, and choose Text as the Column data format.

 

 

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

 

 

Split a Text To Columns Manually (Fixed Width)

You can also position where columns should 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 the text cell (B1) and in the Ribbon, go to Data > Text to Columns.

 

 

2. In Step 1 of the wizard, choose Fixed width and click Next. Fixed width automatically recognizes spaces in the text and splits the data at each space.

 

 

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 should go in which column. In the end, click Next.

 

 

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

 

 

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 in Google Sheets

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

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

 

 

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

 

 

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

 

 

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. Type the formula:

=SPLIT(B1, ";")

 

 

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.