How do I change data that has been entered in a column to a row?
 

I have text data (name, address, city, state, zip) that has been entered on a
spreadsheet in columns down the spread sheet (each criteria in it's own cell
vertically/in columns) and I need each of the above mentioned criteria to be
in it's own cell horizontally so that it can be imported into a database. so
in other words the name, address etc woul read from left to right across the
spreadsheet. I have Mircosfot Excel 2003. I have over 300 names and addresses
to convert. Any idea on how to make this fast and easy would be very
appreciated.

ryoung

I presume your data is in separate cells e.g

A1 = name
A2 = address
A3 = city
A4 = state
A5 = zip
A6 = name
A7 = address etc etc

Highlight cells B1 to F1 (i.e target raange for first address). Then type in
cell B1 whilst cells are highlighted (cell B1 will appear white, rest blue)

=TRANSPOSE(A1:A5)

IMPORTANT. Do not press enter but press CTRL+SHIFT+ENTER. This will put your
data in rows.

Now copy this range i.e B1 to F1 and then paste in cell B6 (i.e for your
next address). Then continue down page...

Any problems, write back

Alex

"ryoung" wrote:

I have text data (name, address, city, state, zip) that has been entered on a
spreadsheet in columns down the spread sheet (each criteria in it's own cell
vertically/in columns) and I need each of the above mentioned criteria to be
in it's own cell horizontally so that it can be imported into a database. so
in other words the name, address etc woul read from left to right across the
spreadsheet. I have Mircosfot Excel 2003. I have over 300 names and addresses
to convert. Any idea on how to make this fast and easy would be very
appreciated.

Not very practical if you have more than one name and address.
there would be more names and address continuing in
A8, A15, A22, A29, A36 etc.

Take a look at a macro solution or worksheet solutions in
http://www.mvps.org/dmcritchie/excel/snakecol.htm-
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Alex" wrote in message ...
ryoung

I presume your data is in separate cells e.g

A1 = name
A2 = address
A3 = city
A4 = state
A5 = zip
A6 = name
A7 = address etc etc

Highlight cells B1 to F1 (i.e target raange for first address). Then type in
cell B1 whilst cells are highlighted (cell B1 will appear white, rest blue)

=TRANSPOSE(A1:A5)

IMPORTANT. Do not press enter but press CTRL+SHIFT+ENTER. This will put your
data in rows.

Now copy this range i.e B1 to F1 and then paste in cell B6 (i.e for your
next address). Then continue down page...

Any problems, write back

Alex

"ryoung" wrote:

I have text data (name, address, city, state, zip) that has been entered on a
spreadsheet in columns down the spread sheet (each criteria in it's own cell
vertically/in columns) and I need each of the above mentioned criteria to be
in it's own cell horizontally so that it can be imported into a database. so
in other words the name, address etc woul read from left to right across the
spreadsheet. I have Mircosfot Excel 2003. I have over 300 names and addresses
to convert. Any idea on how to make this fast and easy would be very
appreciated.