I have a column with a variety of first and last name variations:

Joe Smith
Joe John Jones
Tom Jack Stuart Apple
Maria Lois Lane
Johann Sebastian Bach


I need to be able to sort them on their last name. Note that there is no
comma separating their names and that there can be any number of names,
HOWEVER, the last name always appears LAST.

Probably, in order to make some sort of order out of this mess, it would be
best if I create two separate columns: first names, last name.

Any help will be appreciated.

--


cheers,
Tom G

Hi Tom,

I do not know the lenght of your database, but as a first step I would
recommend using
Data Text to Columns to get each piece of info in the right column ...


HTH
Cheers
Carim

see post at excel.misc

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Carim" wrote in message
ups.com...
Hi Tom,

I do not know the lenght of your database, but as a first step I would
recommend using
Data Text to Columns to get each piece of info in the right column ...


HTH
Cheers
Carim

Hi Tom

Try below, please

Option Explicit

Function Lastname(celle) As String
Dim Lastnamespace As String
Lastnamespace = InStrRev(celle, " ")
Lastname = Mid(celle, Lastnamespace + 1, Len(celle))
End Function

Function Firstname(celle) As String
Dim Lastnamespace As String
Lastnamespace = InStrRev(celle, " ")
Firstname = Mid(celle, 1, Lastnamespace - 1)
End Function

--
Best regards
Joergen Bondesen



"Tom G." wrote in message
...
I have a column with a variety of first and last name variations:

Joe Smith
Joe John Jones
Tom Jack Stuart Apple
Maria Lois Lane
Johann Sebastian Bach


I need to be able to sort them on their last name. Note that there is no
comma separating their names and that there can be any number of names,
HOWEVER, the last name always appears LAST.

Probably, in order to make some sort of order out of this mess, it would
be best if I create two separate columns: first names, last name.

Any help will be appreciated.

--


cheers,
Tom G