Solved: Split name

[Tom Jones]

Hello,

How can I break out of a column name into 2 separate columns. Do not want to use "split to columns" because I need something differant.
First name should be placed in column D and the second, the third, the fourth, etc. ... be in column E. I have several hundred names.
Need a VBA code to automatically start when I insert name in column A.
Then I will copy column D and E in another location (Word or Excel).
Thank you.

[p45cal]

in D1:
=LEFT(A1,FIND(" ",A1)-1)
in E1:
=MID(A1,FIND(" ",A1)+1,999)

copy down if necessary, no need for vba.

[Tom Jones]

p45cal,

Thank you for replay.
Need a small VBA code, cause users when copy that name don't use copy paste special.... and they copy formula ... and ...

Just for that need a VBA code.

[CodeNinja]

This would work...
Sub SplitNames()
Dim l As Long

For l = 1 To Sheet1.Range("A65536").End(xlUp).Row
Sheet1.Cells(l, "D") = Split(Sheet1.Cells(l, "A"), " ")(0)
Sheet1.Cells(l, "E") = Split(Sheet1.Cells(l, "A"), " ")(1)
Next l
End Sub

[Kenneth Hobs]

Right click the sheet tab, view code and paste. If you want to update current data, cut and paste column A data back.
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, r As Range, a() As String
Set r = Intersect(Target, Range("A2", Range("A" & Rows.Count).End(xlUp)))
If r Is Nothing Then Exit Sub

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each c In r
With c
Range("D" & .Row, "E" & .Row).Clear
If IsEmpty(c) Then GoTo NextC
a() = Split(.Value)
Range("D" & .Row).Value = a(0)
Range("E" & .Row).Value = LTrim(Replace(.Value, a(0), vbNullString))
End With
NextC:
Next c

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

[Tom Jones]

Thank you very much both of you.
Kenneth Hobs your code work like a charm.
CodeNinja your code dont meet the criteria for names with 3 or more word.

Thank you.