Last names

[ttcbroer]

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

 

[Richard Schollar]

Hi

try:

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

 

[Stormseed]

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]

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]

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

 

[Stormseed]

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

 

[Richard Schollar]

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]

Mr. Richard, thanks very much for your help.