Text to Column

To separate the contents of one Excel cell into separate columns, you can use the ‘Convert Text to Columns Wizard’. If you copy data from another program and paste it into Microsoft Excel, Excel may condense several columns of data to a single column. You can use the Text To Columns command on the Data menu to put each record in a separate column. The range that you convert may contain many rows, but you can only convert one column of data at a time.


Split cells using Excel’s Text to Columns feature

This Text to Columns option comes in really handy when you need to separate data in one column to several columns in Excel 2013, 2010, 2007 or 2003. The Text to Columns function allows splitting cells’ values separated with delimiters as well as fixed width data type (when all values contain a certain number of characters). Let’s see how each scenario works:

  • How to split delimited text to columns
  • How to separate text data of fixed width


Split delimited text data to columns in Excel

Suppose, you have a list of call center agents information which you download from the server. As you can see in the screenshot below, the first column A lists the serial no (SL) along with agents Names, TL Name, QA Name, AM Name, Current Designation, Date of Joining, Date of Birth, and Blood Group:

raw data

What we want is to split this text into individual columns so that our table has the following columns from left to right: SL, TL Name, QA Name, AM Name, Current Designation, Date of Joining, Date of Birth, and Blood Group.

1. If your table has one or more columns next to the column you want to split, you start by creating new empty columns into which you want to divide cells. This step is needed in order for your existing data won’t get overwritten. If someone has forgotten, I’ll remind you a quick way to insert multiple columns in Excel. Select one column by clicking its header and drag to the right or left to select the number of columns you want to insert. Then right click on the selected columns and choose Inset from the context menu.

Note: If you don’t have any adjacent columns next to the column you want to separate, this step is not actually needed and you can skip it.

2. Select the column you want to split, navigate go to Data Tools tab and click the Text to Columns button.
text to column

3. The Convert Text to Columns wizard opens and in the first step of the wizard, you choose the type of data. Since our entries are delimited with spaces and commas, we choose Delimited type (Fixed width type is explained a bit later). When done, click Next to continue.
convert popup

4. On the next step you specify the delimiters your data contain and the text qualifier.

Setting the delimiters. If your data is separated with one or more delimiters, you select all appropriate options available under the Delimiters section or enter your own delimiter in the Other box.

In our example, we set Space and Comma as the delimiters and check the “Treat consecutive delimiters as one” option. This option would help to prevent excess parsing of data, e.g. if there are 2 or more successive spaces between words.
convert popup2

Specifying the text qualifier. You may need this option if the column you want to split contains some values enclosed in quotation marks or apostrophes and you’d rather not split such portions of text and treat them as a single value. For example, if you choose a comma as the delimiter and a quotation mark (“) as your text qualifier, then any words enclosed in “”, e.g. “California, USA”, will be put into one cell as California, USA. If you select {none} as the text qualifier, then “California will be imported into one column and USA” into another.

Near the bottom of the dialog window, you can see the Data preview section. Before you click Next, it stands to reason to quickly scroll down to make sure Excel has properly converted all of your text data to columns.

5. Just two more things are left for you to do – choose the data format and select the destination for split cells.

You can choose the data format for each of the columns into which the data is separated. By default, the General format is set for all columns. We leave it as is for the first 3 columns and switch to the Data format for the 4th column, which seems quite logical because it lists the arrival dates πŸ™‚

To change the data format for a particular column, click on the column in the Data preview section to highlight it and then set the wanted format under the “Column data format” section.
convert popup3

In the same step of the wizard, you can choose the destination column for the separated data. To do this, click the range selection iconΒ  (in Microsoft terms, it’s called Collapse Dialog icon) to the right of the Destination box and select the far left-hand column where you want the split cells to appear. Regrettably, you are not able to import the separated columns to another spreadsheet or workbook, you would get the invalid destination error if you try to do this. B2

Tip: If you do not want to import some column(s) you see in the data preview, select it and check Do not import column (skip) radio button.

6. Click Finish and you are done!
converted data

Now formatting data for good presentation for mine which is looks like below. You should format after finish this as per your requirement.

Final resutl


Separate text of fixed width into several columns

If your data consists of text or numeric values with a certain number of characters, you can divide such cells into several columns in the following way. But I strongly recommend that please do not use this feature until its extremely necessary. I repeat again, please do not use this feature until its extremely necessary because when you have thousand of rows data, you missed something unintentionally.

For example, we have a table listing the SL and Agent Names, each product SL consisting of 3 characters comes first, before the Agent Name.

Here’s what you do to split such a column into two columns in Excel.

1. Start the Convert Text to Columns wizard as explained in the above example. In the first step of the wizard, choose Fixed width and click Next.
2. Set the columns’ widths using the data preview section. As you see in the screenshot below, a vertical line represents a column break and to create a new break line, you simply click at the needed position. Double-clicking a break line removes the break, and if you want to have a break in some other position, click the line and drag it with the mouse. In fact, you can see the same instructions in the upper part of the dialog window πŸ™‚
convert2 popup

Since our SL contain 3 characters each, I’ve set the break line exactly at this point, as shown in the image above. In this scenario, you see, why I’m suggest you to not use this feature. Our column header get a , (comma) because we separate by 3 character.

3. In the next step, choose the data format and destination for the split cells as explained in the previous example, and then click the Finish button to complete the process.



Download Documents

Download Excel File

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>



clearPost Comment