 |
|
|
|
|
Download the associated Workbook for this lesson
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 us to easily separate values that reside in a single cell and have them placed in their own cells.
Lets say we have a very long 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. I will use this as an example as this should give you a good idea of its capabilities.
Select the cells A1:A4, which is 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, let's place the following formula in cell C1: =""&D1&" "&E1&" "&F1&" "&G1. (Note - you may have to widen column C)
Double click the Fill handle of (or copy down) cell C1
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
Now copy the original data to cell E1 and repeat steps 3,4 and 5. But this time only import the phone numbers at step 5. You will then need to repeat steps 10, 11 and 12 as the phone numbers have spaces in between them.
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.