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
-
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.
