PDA

View Full Version : Turning cariage returns into new column in excel



diomedes4545
03-16-2010, 01:25 PM
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
03-16-2010, 01:55 PM
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
03-16-2010, 02:36 PM
Thta produces a 2D array Mark.

But you can simplify it like so



rCell.Resize(, 2).Value = Split(rCell.Value, Chr(10))

GTO
03-16-2010, 03:30 PM
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...