Extract last names with variable formats?

[JP Romano]

Hi... I need some help with either a formula or vba routine that will extract last names for a comparison. The names can come into my spreadsheet as any of the following
John Doe
John J Doe
Jonh Jason Doe
John Jason Lee Doe
I've already stripped out all of the suffixes (Jr., Sr, CFA, MD, etc) and am using the following formula. The problem is, for some reason some of the results are blank. I copied this formula from another sheet, but am having trouble following exactly what it's doing. Any help is GREATLY appreciated!

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

Cell J2 contains the full user name, which is to be compared to another name value from another system which will contain a similar formula.

THANK YOU!

[debasisdas]

findout the position of the space from the right side .
From that position+1 read to the end for the last name.

[rpicilli]

Hi there.

You can use the property lastindexof to find the last space and get the last word

The example below show you how to get the last word typed in TextBox1 and put in Label1.Text

I hope this help you.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Me.Label1.Text = Me.TextBox1.Text.Substring(Me.TextBox1.Text.LastIndexOf(" ") + 1)
    End Sub

[JP Romano]

Thanks a million for the help!