Split text in a column to multiple columns

  • Thread starter Thread starter milo
  • Start date Start date
M

milo

Can I split text in a column like the example below into
three columns?
Column = Column1 Column2 Column3
1 1234 John W. Doe = 1 1234 John W. Doe

Thanks,
milo
 
Okay this isn't the most elegant code, but I'm still learning, and will
always welcome tips on way to make my code more efficient. This should
work assuming your data is in column A.


Columns("B:E").Select
Selection.insert Shift:=xlToRight
Columns("A:A").Select
Application.DisplayAlerts = False
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False,
FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1),
Array(5, 1))
Application.DisplayAlerts = True
Columns("C:C").Select
Selection.insert Shift:=xlToRight
Range("C1").Select
Do
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[1],"" "",RC[2],""
"",RC[3])"
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(0, -2) = ""
Range("c:C").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Columns("D:F").Select
Selection.delete Shift:=xlToLeft
 
the easiest way is to highlight all the colums and then go
to the data menu and go to 'text to columns' and then hit
next and put the line where you want to seperate them.
 
Back
Top