# Finding last name problem

#### hatstand

##### Well-known Member
I'm trying to amend this formula to find the last name in a cell that has the a fist name and second name, as copied from cpearson, a big thank you too him :

=TRIM(RIGHT(K239,LEN(K239)-IF(ISERROR(FIND(" ",K239,FIND(" ",K239,FIND(" ",K239,1)+2))),LEN(K239),FIND(" ",K239,FIND(" ",K239,FIND(" ",K239,1)+2))-1)))

I have already amended it to look for spaces rather than "," seperators. But it will only work when there is a middle name. Is it possible to change it to find the last name only?

Any help would be much appreciated.

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### pgc01

##### MrExcel MVP
Hi hatstand

Assuming the last name comes after a space, this formula will extract it no matter how many names the person has:

Code:
``=RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))``

Hope this helps
PGC

#### Andrew Poulsom

##### MrExcel MVP
If A1 contains John Smith or John Peter Smith (or even Mr John Perter Smith) this:

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

will return Smith.

#### hatstand

##### Well-known Member
A big thanks you

Thanks for your help. They work perfectly.

#### barry houdini

##### MrExcel MVP
Here's an alternative, assuming your surname has no more than 99 letters

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99))

Replies
17
Views
836
Replies
2
Views
407
Replies
5
Views
291
Replies
12
Views
459
Replies
10
Views
328

1,171,827
Messages
5,877,778
Members
433,287
Latest member
amna_shahbaz

### 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.

### Which adblocker are you using?

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

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