Ozgrid, Experts in Microsoft Excel Spreadsheets


Text to Columns and Importing Text Files


Excel Training Level 3 Lesson 4-Excel 97-2003


Text to Columns and Importing Text Files

This seems to be another one of Excels little known or used features, but as you will see it can be a very handy little tool. The purpose of this feature is to allow a user to easily separate values that reside in a single cell and have them placed in their own cells.

Lets say we have a list of names, addresses and phone numbers. The problem is, instead of having the names in one column, the addresses in another column and the phone numbers in another somebody has entered them all in the same cell. To make matters even worse, you have been given the task of separating them all so that there is a First Name, Surname, Address and Phone column! This is where the Text to columns feature can be invaluable. We will use this as an example as this should give you a good idea of its capabilities.  Open the workbook ExcLev3L02c.xls

  1. Select the cells in the Column that hold the names, addresses and phone numbers and make a copy of them on another Worksheet in cell A1.

  2. With the copied data still highlighted, go to Data>Text to columns  

  3. Check the Delimited option and click Next.

  4. Select the Space option only and click Next  

  5. Now holding down the Shift key click on each column heading (General) except the first and last names.  

  6. With all the other columns selected, click the Do not import columns (skip) and click Finish  

  7. As you will see we now have two columns of data, one for first names (column A), and one for surnames (column B)

  8. Go back to the original data again and copy it. This time paste it into cell D1  

  9. Repeat steps 3,4 and 5 but at step 5 use the Do not import columns (skip) option on all columns except the Address columns In other words skip the first name, second name and phone number columns. Click Finish 

  10. Now as we have left column C blank we place in the formula: =D2 &" "& E2 &" "& F2 &" "& G2 in cell C2

  11. Double click the Fill handle of (or copy down) cell C2

  12. With all the formulas selected in column C, copy, then go to Edit>Pastespecial and select Values. Now delete Columns D, E, F and G

This can turn a 10 hour job into five minutes or less!

As you would have seen while in Step one of the Text to column Wizard there is also a Fixed width option we can use. This option will allow us to place in Column breaks at any point.  Using this we could go as far as separating our data into separate characters. The steps in this Wizard are fairly self-explanatory. If we cannot achieve our result in one pass through the Wizard we need to "step outside the box" and use a bit of lateral thinking.