Gary,

Thanks for your help.

Max

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
and if you wanted to trim the spaces
Option Explicit

Sub split_names()
Dim ws As Worksheet
Dim sName As Variant
Dim i As Long
Dim lastrow As Long
Dim sAuthor As Long, sTitle As Long
sAuthor = 3 ' column C
sTitle = 4 ' column D
Set ws = Worksheets("sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow
sName = Split(ws.Range("A" & i), ":")
ws.Cells(i, sAuthor).Value = Trim(sName(0))
ws.Cells(i, sTitle).Value = Trim(sName(1))
Next

End Sub

--


Gary


"Max Bialystock" wrote in message
...
A cell contains an author's name and a book title separated by a colon.



Charles Dickens : Great Expectations



I need to get "Charles Dickens" into sAuthor

and "Great Expectations" into sTitle.



In this case there is always a space before and after the colon.