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
Open the workbookExcLev3L02c.xls
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.
With the copied data still highlighted, go to Data>Text to columns
Check the Delimited option and click Next.
Select the Space option only and click Next
Now holding down the Shift key click on each column heading (General) except the first and last names.
With all the other columns selected, click the Do not import columns (skip) and click Finish
As you will see we now have two columns of data, one for first names (column A), and one for surnames (column B)
Go back to the original data again and copy it. This time paste it into cell D1
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
Now as we have left column C blank we place in the formula: =D2 &" "& E2 &" "& F2 &" "& G2 in cell C2
Double click the Fill handle of (or copy down) cell C2
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.