My Blog

Text to columns

Though I have heard about this feature, I never used it until I had a task of splitting up an address column. Microsoft ExcelÆs Text to column feature allows you to split data to multiple columns easily.
LetÆs say you have thousands of names in format ôFirst, Last Nameö in a single column and your task is to sort the list by last name. Wondering how? Use ExcelÆs Text to column feature to split the name into two columns by specifying comma as a delimiter. So Simple! Right?

Let me demonstrate this feature by splitting an address block.

Below is a screenshot of an actual data on which we are going to work:

1. Select the entire column (by clicking on the column heading), or a group of cells whose contents you need to split. In our case, I have selected cell B2.

2. From the Data menu, select Text to Columns.

3. You will be displayed with a window similar to one shown below to choose the type on how you want the contents to be split:

Delimited: If you want the contents to be split where ever there is a Tab, Semicolon, comma, space, or any other delimiter such as a new line.

Fixed width: When you do not have any such delimiters but can specify place where the split needs to occur. Here is an example at Microsoft Office Online.

In our scenario, we need to choose delimiter as a new line to split the contents line by line.

4. You should be a similar window once you select Delimited and press Next.Choose the appropriate delimiter from the resulting window. As I want the split to occur line by line, I need to choose new line character which is not readily available. So I choose Other, and in the box next to it, I press ôCtrl + Jö simulateneously which indicates to excel that a new line character should be considered as a delimiter.

One interesting feature I noted here is Text qualifier option. Assume that you have a Company name such as ôCompany A, Incö which you wouldnÆt want to split by specifying comma as a delimiter. In such a case, put a check next to ôTreat Consecutive Delimiter as oneö and select double quote or a single quote whichever as available to the split not to occur.

5. Click Next to display the following window:

Here you can choose the data format that you would like to apply by selecting the column as shown above and choosing appropriate format. Then you need to specify from where the results are to be placed by specifying the cell address in the destination box. For example, if you are working on a group of cells say B1 to B10 and would like to paste the results in the immediate next column say C1 to C10, just selecting C1 in the destination box would do the trick. Check the below screenshot that shows how wonderfully Microsoft ExcelÆs Text to Column feature has split the address block:

Trackback This Post | Subscribe to the comments through RSS Feed