Parsing out Last Name 2

[goolawah]

I need a rough and ready way to parse out Last Name in an excel file where the number of words in the name field is unknown, but I want to get the last one. This will then enable me to sort by surname.

For example "Robert Louis Stevenson" becomes "Stevenson"
"Robert Ludlum" becomes "Ludlum" and "Simenon" remains "Simenon"

I'm not worried about odd ones like "van de staadt"

I have managed it as per the following link Surname but it's not very elegant.

This must be a common problem and I wonder how others have solved it?

 

[Gavona]

I never got around to solving it until now. Here is my solution. I was lazy and so it will only return the first 99 letters of the surname.

=MID(E2,FIND("[",SUBSTITUTE(E2," ","[",LEN(TRIM(E2))-LEN(SUBSTITUTE(TRIM(E2)," ",""))))+1,99)

Basically I strip out all the spaces using Substitute().
Compare length before and after.
This tells me how many spaces.
I replace the last of these with a special character - using Substitute().
Then I simply Find the position of the special character.
And use mid() to return up to 99 characters to theright of it.


Gavin

 

[ShabanaHafiz]

You can also use RIGHT function:

 =RIGHT(E2,IF(ISERROR(FIND(" ",E2)),LEN(E2),LEN(E2)-FIND("#",SUBSTITUTE(E2," ","#",LEN(E2)-LEN(SUBSTITUTE(E2," ",""))))))