Extracting Surname field

[HelenL]

I have data where I need to extract the Surname only, but in some cases there is either 1 or 2 middle names, or no middle name.
examples John George Evans, John Evans, Sue Mary Jo Ellis, Jean Ellis.
Hope someone can help please!

 

[WaterGypsy]

Hi Helen

I would use InStrRev to find the first space from the right and then use that to get the rightmost characters......

... there have been a couple of threads on this subject recently if you want to search for them

regards

Kevin

 

[HelenL]

Thank you. I did search but go no results, so perhaps I am using wrong search word? I have used various to get the first space, but unsuccessful in tracking if 2 spaces exist as per example in order to get Surname only. Could you please give me an example to use your syntax on say Mary Jane Elizabeth ELLIS to extract ELLIS. Many thanks. Helen

 

[VoG]

Here is a User Defined Function. Press ALT + F11 to open the Visual basic Editor, Insert > Module and paste in

Function Surname(r As Range) As String
Surname = Right(r.Value, Len(r) - InStrRev(r.Value, " "))
End Function

then press ALT + Q.

To use it to extract the surname from the name in A1

=surname(A1)

 

[HelenL]

Thank you. You are a genius. Special thanks too for not assuming any knowledge of VB on my part!

 

[HelenL]

Sorry to trouble you. I tested successfully on later version of Excel and the data cannot be moved from system on Excel 97. Using suggestion solution, I get Compile Error Sub or Function not defined highlighted on the (r) of Len(r) in code below. Any thoughts round this please?

Function Surname(r As Range) As String
Surname = Right(r.Value, Len(r) - InStrRev(r.Value, " "))
End Function

 

[VoG]

Try this formula suggested by Richard Schollar here http://www.mrexcel.com/forum/showthread.php?t=464034

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

 

[HelenL]

Thank you Peter. That's perfect.