Pick the last name from a cell

[hhammash]

Hi,

How can I pick only the last name from a cell?

I have a full name in a cell, let's say in A2:

John K Smith how can I pick only Smith and put it in cell E2?

I have more than 3000 names in Column A.

Thanks

 

[Krishnakumar]

Hi,

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),99))

 

[hhammash]

Thanks a lot Krish,

It worked perfectly. Would you please explain it?

Specially the REPT and the 100 and the 99.

Thanks a lot.

 

[Aladin Akyurek]

No need for unconventional constants...

=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

 

[sykes]

............ or if you wanted it to run off a command button on the worksheet whenever it was pressed attach this code to CommandButton1;

Private Sub CommandButton1_Click()
Dim rw As Integer
rw = Me.Range("A65536").End(xlUp).Row

    For Each c In Me.Range("A2:A" & rw)
        c.Offset(0, 4).Value = Right(c.Value, (Len(c.Value) - InStrRev(c.Value, " ")))
    Next
End Sub