VBA: Extract last name

[FryGirl]

mI have a column of names but only want to retain the first name. I've tried using this evaluate in VBA, but it keeps giving me a #VALUE!.

Sub RemoveName()
  With Range("J2", Cells(Rows.Count, "J").End(xlUp))
    .Value = Evaluate(Replace("IF(ISERROR(FIND("","",@)),@,MID(@,FIND("","",@)+2,LEN(@))", "@", .Address))
  End With
End Sub

Contacts Outlook.csv
	J
26	Peter
27	Shepherd, Alan
Contacts Outlook

This is a small sample and in the cell I just want to be left with the first name.
Peter
Alan

Also, as an alternative, how could I do this on just a single cell with a double click?

 

[RoryA]

You're missing a closing bracket at the end of your formula.

 

[FryGirl]

That's Perfect Rory. Glad your eyes are younger than mine. Any thoughts on the double click?

 

[RoryA]

Lots of ways - here's one:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Cancel = True
  With Target
    Dim v
    v = Split("," & .Value, ",")
    .Value = Trim$(v(UBound(v)))
  End With
End Sub

 

[FryGirl]

Thanks Rory. I came up with this and it seems to work as well.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 10 Then Exit Sub
    Dim x As Long: x = InStr(1, Cells(Target.Row, 10), ",") + 2
    Cells(Target.Row, 10).Value2 = Mid(Cells(Target.Row, 10), x, 100)
End Sub