How to use Text to Columns in Excel

by | Nov 25, 2019 | Excel Tips & Tricks

text to columns

Data doesn't always come in a nice table layout, especially, when you retrieve it from an external source like a web site. Thanks to the Excel Text to Columns feature, you can split data into multiple columns. In this article, we are going to show you how to use the Excel Text to Columns feature to easily structure your data.

Download Workbook

You can use Text to Columns in two ways. Data can be split either by a specified delimiter character, or after a specific number of characters. The splitting mode and other properties can be configured in the Convert Text to Columns Wizard. Let's see how you can use the wizard and split text into columns with the help of a few examples.

Text to Columns with Delimited Text

Delimited text is essentially how CSV (Comma-separated values) files store data. In a CSV file, columns are delimited by a comma (,). Text to Columns feature can split a text string delimited with any characters. The wizard presents you an option where you can change the delimiter character. Follow the steps below to do this:

  1. Start with selecting your data. You can use more than one cell in a column.
  2. Click on the Text to Columns icon under the Data tab in the Ribbon.
  3. On the first step of the wizard, you have 2 options to choose from - These are slicing methods. Make sure that the Delimited option is selected and click the Next button to continue.text to columns
  4. Choose the delimiter and text qualifier characters. A text qualifier essentially wraps a string and marks it as a text string. Use this option if your data has text blocks that contain delimiter characters as well. This way, the text blocks will remain intact.
    text to columns
  5. At this point, you will see a preview of how your data will look like after it's split. Click the Next button to proceed to the last step.
  6. You can format your columns specifically like a text or date and select a Destination cell to determine where the columns will begin.
  7. Click the Finish button to apply your settings and perform the splitting.

One thing to mention here is that Excel keeps the options you've used. As a result, you can use Text to Column feature again quickly when you need to update the data.

Fixed Width

Use the Text to Columns feature in fixed width mode if your data is merged based on a pattern. This type of splitting is good for dealing with codes like serial numbers, invoices, or part numbers. Let's see how you can perform Text to Columns feature in fixed width mode.

  1. Select your data and run the wizard.
  2. This time, select the Fixed Width option in Step 1
  3. Click Next to continue.
  4. In Step 2, determine the widths by adding lines into the Data preview. To do this, simply click on the pane to add lines. In the example below, we added two lines: after 2nd character and after 5th character.
  5. When you are satisfied with how the data would be split, click Next to continue.
  6. Step 3 is identical for both delimiter and fixed width modes. However, in our example, this time we set the 2nd column as Text; because if we didn't, Excel would convert them into numbers by removing the zeros (0).
  7. Click Finish button to see the results on the worksheet.