Split text into columns

[Mervyn Thomas]

Has anyone got any code that would split a name in one cell into three
columns. I have seen somewhere how to look for the space and so split it
up! An example of my data is:
Rev Christopher Dent

 

[Ryan H]

I assume your names are in Col. A and you want the First Name in Col.B,
Middle Name in Col.C, and Last Name in Col.D. This will do it for you.

Sub SplitNames()

Dim rng As Range

For Each rng In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
Range(Cells(rng.Row, rng.Column + 1), Cells(rng.Row, rng.Column +
3)) = Split(rng, " ")
Next rng

End Sub

Hope this helps! If so, let me know, click "YES" below.