Newbie question: how to convert one column to multiple columns?

  • Thread starter Thread starter David E.
  • Start date Start date
D

David E.

Anyone,

I have a spreadsheet of about 50 names/addresses in which all of the data is
in a single column, i.e.:

name1
address1
city1
state1
zip1
name2
address2
....
etc.

I'd like to convert this spreadsheet so that the names are all in column A,
the addresses in column B, etc. I'm certainly willing to select all five
related cells for a given person (I would need to do that 50 times), but
once those five cells are selected, is there a way that I can recast those
five cells from being in the same column into multiple columns?

Thanks so much!

David
 
You can easily do that with a formula and some help columns,
assume the first name starts in A2. In a help column let's say D2
put this formula

=OFFSET(INDIRECT("A"&ROW(1:1)*5-3),COLUMN(A2)-1,)

copy across 5 columns, then while still selected grab the lower right corner
of H2
and copy down as long as needed.
Note that if the data is in another column the only thing in the formula you
should change is

"A" for column and ROW(1:1)*5-3 for the row where the data starts

so if your data starts in C20 use

=OFFSET(INDIRECT("C"&ROW(5:5)*5-5),COLUMN(A2)-1,)

Once you are done,select the help columns and copy, then in place do
edit>paste special as values
finally after checking everything went well delete the original column
 
a. Try also : http://tinyurl.com/wpj0
which outlines the steps for such situations

b. As for your "sub" query below:
I'm certainly willing to select all five
related cells for a given person (I would need to do that 50 times), but
once those five cells are selected, is there a way that I can recast those
five cells from being in the same column into multiple columns?
Click to expand...

Select 5 vertical cells (say A2:A6) > Copy
Right-click on say B2 > Paste special > Check "Transpose" > OK

A2:A6 will be "transposed" into B2:F2
 
Hi,

STEPS.
1. Click the cell which contains the data.
2. Go to the Menu and click Text to table.
3. Click Next
4. Specify which column to seperate by dragging the line.
5. click ok

Regards,

Corine
 
Back
Top