HELP! Split cell contents to new rows

[nyarce]

I know how to split cell contents to columns, but how do you tell excel to
take the cell contents and insert into new rows. Each name is separated by a
comma and I need each name to appear in its own row beneath that cell.
PLEASE HELP!

 

[Luke M]

Copy, paste special-transpose (now in columns). Data - Text to columns.
Copy, paste special- transpose back to rows.

 

[dlw]

you need to use text functions like FIND, MID, LEFT, RIGHT to chop up the
cell, tell us what the data looks like and we can give specific examples.

 

[Eduardo]

Hi,
Highlight the column, text to column, delimited,check the box that says
comma, finish

 

[nyarce]

Nancy Smith, Joe Pelegro, Janie Simmons are all in one cell. I need excel to
create a new row below Nancy Smith and insert Joe Pelegro in there by himself
and the same for Janie Simmons so that it looks like:

Nancy Smith
Joe Pelegro
Janie Simmons

I also need to make sure the new rows to not overwrite/delete the contents
of the existing rows beneath the original entry of Nancy Smith, Joe Pelegro,
Janie Simmons, hence me needing excel not only to move these contents but
create new row for each name.

 

[Richard Howell]

Hi,

I am looking to do exactly the same thing - I was so excited to find this forum entry and then disappointed to see no answer below!

I effectively want to ask excel to do something similar to 'Text to Columns' BUT I want 'Text to Rows'.

I can't believe there isn't a way to do this!? There is a lot of data in multiple rows which all need 'splitting downward'. It is not feasible to transpose back and forth really - I may as well do it manually. I am happy, however, to insert the appropriate number of rows manually, prior to asking for 'text to rows'.

SO-
TEXT TO ROWS - can it be done?

Thanks in anticipation,
Richard

 

[Gord Dibben]

Go ahead and Text to Columns to get the A column cells broken across the rows.

Now, to transpose all into one column(text to rows) you can use worksheet
formula

Assuming three columns per row. Adjust the formula to suit.

In D1 enter this formula and drag/copy down.

=INDEX(A:C,ROWS($1:3)/3,MOD(ROWS($1:1)-1,3)+1)

1 2 3
4 5 6

becomes

1
2
3
4
5
6


Or after Text to Columns, use this macro.

Sub rowstocolumn()
'bob phillips....March 15th, 2010
'must be no blanks in the matrix
With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = LastRow To 1 Step -1

For J = LastCol To 2 Step -1

.Rows(i + 1).Insert
.Cells(i, J).Cut .Cells(i + 1, "A")
Next J
Next i
End With
End Sub


Gord Dibben MS Excel MVP

 

[Rick Rothstein]

Assuming the delimiter between names is **always** a comma followed by a
space (if not, then replace the ", " in the Split function with the
delimiter text that is consistently located between names), you can use the
following simple macro to do what you want. Simply select the cell with the
names in it and run this macro...

Sub SplitAndTranspose()
Dim N() As String
N = Split(ActiveCell, ", ")
ActiveCell.Resize(UBound(N) + 1) = WorksheetFunction.Transpose(N)
End Sub

And, if you have several cells across a row that have names in them that you
want to do list down their respective columns, simply select those cells (it
does not matter if they are contiguous or not) and then run this macro
instead...

Sub SplitAndTranspose()
Dim Cell As Range, N() As String
For Each Cell In Selection
N = Split(Cell, ", ")
Cell.Resize(UBound(N) + 1) = WorksheetFunction.Transpose(N)
Next
End Sub