Changing rows into columns in excel

[Gannon]

I have some data in an excel sheet that i need to convert from a row to a column because i want to import that data to import into a database which accepts only this format. Consider the following example how my data looks like:
Graeme smith 220 town1, City1
Andrew clarke 810 town2, City2

I want this data to look like

Graeme
smith
220
town1, City1
Andrew
clarke
810
town2, City2

[Eric B]

This can be done very easily you can transform any row into columns in excel you just have to select the whole row which you want to convert to columns and then right click in the column where you want the copied data select paste special under that you will see the Transpose checkbox and then click OK. Hope you got the solution.

[JonathanD]

So you want to convert a row from your Excel sheet to a column. Don't worry excel provides some inbuilt functions such as paste special. you will have to use this function to change a row into column. For this just follow the following instructions which are listed below.

1.Select the whole row which you want to be converted to column.
2.Copy it Either use CTRL+C or right click the selected row and click copy.
3.Now , Right click on the top most cell of the column in which you want to copy whole row.
4.You will see an option named "Paste special", which will open a small new window , at the bottom of this new window there is an option called 'Transpose' check that.
5.Now, click on the OK button.

I hope you got the desired result.

[ThoMas321]

It would be a tedious job to move the data manually from a rows to column. In order to save the time and energy. The paste special command could be used to quickly transpose a column of data to a row of data or viceversa.

* Copy the row of data.
* Before pasting the copied data, right-click your first destination cell i.e.the first cell of the column into which you want to paste your data, and then click Paste Special.
* In the Paste Special dialog box, check the Transpose checkbox, and then click OK.

[Gannon]

First of all i would like to thank you all for giving me replies in this matter , All your solutions worked for me, it works great for one row at a time is there any way to get it to work for all the rows or will this require a macro

[Sam.D]

Yes, Transposing more than a single row at a time will require a macro. Try this macro, Change the second sheet to the sheet name you are transposing to. Do not forget that there are only 256 columns, so select only 256 rows at a time.

Sub transp()
Dim n As Long
For n = 1 To Selection.Rows.Count
Selection.Rows(n).Copy
Sheets("Sheet2").Cells(1, n).PasteSpecial Transpose:=True
Next n
End Sub