Turning cariage returns into new column in excel

[diomedes4545]

I have a problem where i have 2 names in one cell seperated by a carriage return i need to remove the return and place the second name in a seperate column i've been trying to work on a simple macro but can't quite make it work

[GTO]

Just by example, try this in a junk/test copy of your wb. Select some of the names and run:

Sub exa()
Dim rCell As Range
    
    If Selection.Columns.Count = 1 Then
        For Each rCell In Selection
            If InStr(1, rCell.Value, Chr(10)) <> 0 Then
                rCell.Offset(, 1).Resize(, 2).Value = _
                    Array(Split(rCell.Value, Chr(10))(0), Split(rCell.Value, Chr(10))(1))
            End If
        Next
    End If
End Sub

Does that help?

Mark

[Bob Phillips]

Thta produces a 2D array Mark.

But you can simplify it like so

[vba]

rCell.Resize(, 2).Value = Split(rCell.Value, Chr(10))
[/vba]

[GTO]

Ahh, that is of course better; I unnecessarily 'fancied' it :-( Thank you, as it just didn't occur to me to use the entire return of Split.

I do not see the 2D point though? As I look at it, in essence, I (wastefully admitted) would be returning the same 1D from Array?

I'll look tonight (my tonight), gotta drag my way-late self to bed...