Splitting multi-line cells into separate rows

I did a search on "split" but was not able to find a solution for this one.

I've got a column of names but some of the cells contain multiple - up to 20 - lines of names and I'd like to create another column containing all of those names so that I can de-dupe it and the use it for getting totals from another tab.

The cells with multiple lines have "#. " in front of the names in a format like this:
ROW----- CELL DATA
1-------- OBAMA
2-------- HILLARY CLINTON
3-------- MCCAIN
4-------- 1. OBAMA
--------- 2. HILLARY CLINTON
--------- 3. MICKY MOUSE
5-------- 1. DAFFY DUCK
--------- 2. MINNIE MOUSE

So I would like to end up with:

row-cell data
1---OBAMA
2---HILLARY CLINTON
3---MCCAIN
4---OBAMA
5---HILLARY CLINTON
6---MICKY MOUSE
7---DAFFY DUCK
8---MINNIE MOUSE

Any ideas?
Thanks for having such a great source of shared info!!

Hi jazzper

This is easier in vba.

Assuming the data in column A starting in row 2, the code writes the result in column B, starting in row 2.

Sub Lines2Rows()
Dim rList As Range, rCell As Range
Dim lRow As Long, vLines As Variant

Set rList = Range("A2", Range("A" & Rows.Count).End(xlUp)) ' list
lRow = 2
For Each rCell In rList
    If rCell <> "" Then
        vLines = Split(rCell, vbLf)
        Range("B" & lRow).Resize(1 + UBound(vLines)).Value = Application.Transpose(vLines)
        lRow = lRow + UBound(vLines) + 1
    End If
Next rCell
End Sub

HTH
lecxe

Thanks!!

I've never done any VBA stuff before but I'm guessing there's instructions on numerous posts here-

I've never done any VBA stuff before but I'm guessing there's instructions on numerous posts here-

These are step by step instructions to run the code:

- Activate the vb editor (ALT-F11)

- Menu Insert>Module (if it's the first module it will be called module1).

- Paste the code into the module.

- Click on the code and press F5 or the Run button.

Thanks again -- very nice!!

Hello!

This code was really useful for me too but I need help with the following:

Supose I have each multi-line cell classified by the cel next to the left, for example:

Name Multi-Line Cell
Presidents | Obama, Bush, Clinton
Countries | USA, France, Canada

Suppose there was a forced line break after each comma.
Then I wish the Macro to throw something like this:

Presidents | Obama
Presidents | Bush
Presidents | Clinton

Countries | USA
Countries | France
Countries | Canada

The code you posted helped me partially but I'm getting nuts trying to deal with the 'for' loops. Any suggestions on how to modify it to achieve these results? Thank you in advance!