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:

Is the text too long to fit in within the standard size a cell?

Option 1: Adjust the column width.

• Double-click the left column border in the column header to set the column to Best Fit. (Or)
• Drag the left column border in the column header to the width you want.

If the text is too long, you may have to scroll left to right to view all the row contents.

Option 2: Resize the contents to fit within the cell with these steps.

• Select the cell with text that’s too long to fully display, and press [Ctrl]1.
• In the Format Cells dialog box, select the Shrink To Fit check box on the Alignment tab, and click OK.

This might shrink the text to sacrificing legibility.

Option 3: Wrap the text.

• Select the cell with text that’s too long to fully display, and press [Ctrl]1.
• In the Format Cells dialog box, select the Wrap Text check box on the Alignment tab, and click OK.

This might increase the height of the cell.

Best Option: Insert a space in the cell next to the cell where you have the content. Excel should automatically truncate the long text display whereas your text will still be intact within that cell.

See image below:

Hope it helps.