# Last names

#### ttcbroer

##### New Member
What function do I need when the result must be the lastname

A1. Jan Janssen
A2. John van Dijk

Result must be

B1. Janssen
B2. Dijk

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### Richard Schollar

##### MrExcel MVP
Hi

try:

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

#### Stormseed

##### Banned
Try,

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

Edit:
Provided the value you want to find the last word is in cell A1.

#### Stormseed

##### Banned
Hi

try:

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

Hi Mr. Richard, I guess, incase I change the 50 to 20, the REPT() function would only replicate the text in the given string 20 times, correct ? In this case, my last word should only be a maximum of 50 characters, I reckon.

#### Richard Schollar

##### MrExcel MVP
Yes correct. You can adjust the 50 to 100 (or whatever) if you will have very long last names

#### Stormseed

##### Banned
what would be the maximum limit ? I guess 255, is it ?

#### Richard Schollar

##### MrExcel MVP
No I think REPT will accept a lot bigger numbers than that. The reason why you might not want to do this is that Excel is creating a new string 9with all the spaces) in memory and this takes resources and thus time. To keep things running smoothly and quickly you should just use a reasonable number (which I figured to be 50).

#### Stormseed

##### Banned
Mr. Richard, thanks very much for your help.

Replies
1
Views
210
Replies
1
Views
120
Replies
3
Views
193
Replies
2
Views
196
Replies
2
Views
236

1,190,562
Messages
5,981,697
Members
439,731
Latest member
auraitsuka

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