Text to Columns

[Dendy]

I have a column of text in rows and would like to move it to columns.
I cannot locate how. I know it must be possible

 

[Rick Rothstein \(MVP - VB\)]

Select the rows of text in your column that you want to move to columns,
then click Edit/Copy from Excel's menu bar, then click into the first cell
you want to put the data transposed data into (this cannot be one of the
select cells by the way), then click Edit/PasteSpecial from Excel's menu
bar, click the box at the bottom labeled Transpose and click OK.

Rick

 

[Dave Peterson]

Maybe...

Select the range
edit|copy

go to a new location (don't overlap the original location)

Edit|paste special|click transpose.

 

[Dendy]

Select the rows of text in your column that you want to move to columns,
then click Edit/Copy from Excel's menu bar, then click into the first cell
you want to put the data transposed data into (this cannot be one of the
select cells by the way), then click Edit/PasteSpecial from Excel's menu
bar, click the box at the bottom labeled Transpose and click OK.

Rick




Thank you so much

 

[Dendy]

Maybe...

Select the range
edit|copy

go to a new location (don't overlap the original location)

Edit|paste special|click transpose.

 

[abrusey]

Dave-

I'm trying to do a similar process however I have several entries separated
by two rows within one column that I need converted to several columns.
Example below. The "paste special" - "transpose" works for only one set of
data at a time. Is there a way to select the entire column of data to
transpose to several columns?

Sample Data:

Jim Jones
Producer
Weekend Today
30 Rockefeller Plz, Ste 1140-E, New York NY 10112-0002


Sarah Evans
Features Editor
Woman's World
270 Sylvan Ave, Englewood Cliffs NJ 07632


Any help is greatly appreciated!
-Amanda

 

[Dave Peterson]

This will "flatten" your file.

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim myBigRng As Range
Dim myArea As Range

Set wks = ActiveSheet

With wks

Set myBigRng = Nothing
On Error Resume Next
Set myBigRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) _
.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If myBigRng Is Nothing Then
MsgBox "Nothing found in column A"
Exit Sub
End If

For Each myArea In myBigRng.Areas
myArea.Copy
myArea.Cells(1).Offset(0, 1).PasteSpecial Transpose:=True
Next myArea

On Error Resume Next
.Range("b:b").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You'll still want to parse that last line into separate columns. Maybe you
could use data|text to columns, delimited by commas?????

 

[tom e]

Thanks for the tip Dave. I used your macro and it works like charm. Is there
a way to create a macro that converts a column of data by every 5 rows and
then put it in seprate columns. The data is a list of names, Company,
Address, email, and phone.

 

[Roger Govier]

Hi Tom

If all your data is contiguous down column A, but in sets of 5, you don't
have to use a macro, you can do it with formulae.

In say C1 enter
=INDEX($A:$A,COLUMN(A1)+5*(ROW(A1)-1))
Copy across through D1:G1
Copy C1:G1 down as far as required.

To "fix" the data, copy columns C:G>Paste Special>Values and the formulae
will be replaced with the text contents
--
Regards
Roger Govier

Thanks for the tip Dave. I used your macro and it works like charm. Is
there
a way to create a macro that converts a column of data by every 5 rows and
then put it in seprate columns. The data is a list of names, Company,
Address, email, and phone.

 

[tom e]

You just saved me several hours of mental anguish. Thanks alot!