# Pick the last name from a cell

#### hhammash

##### Board Regular
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

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Krishnakumar

##### Well-known Member
Hi,

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

#### hhammash

##### Board Regular
Thanks a lot Krish,

It worked perfectly. Would you please explain it?

Specially the REPT and the 100 and the 99.

Thanks a lot.

##### MrExcel MVP
No need for unconventional constants...

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

#### sykes

##### Well-known Member
............ or if you wanted it to run off a command button on the worksheet whenever it was pressed attach this code to CommandButton1;
Code:
``````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``````

Replies
4
Views
551
Replies
6
Views
276
Replies
4
Views
411
Replies
24
Views
1K
Replies
35
Views
1K

### Forum statistics

1,171,686
Messages
5,876,893
Members
433,217
Latest member
Muhammad Tanzeel Ur Rehma

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back