Full Name to Last Name, First Name Middle Name (or initial)

[blawless]

Hi there,
I have not located a post that helps me resolve this, and I would be so grateful to receive some help.

This is the data I have in one column:

Alfredo C. Piatti
Antonin Dvorak
Antonio Vivaldi
Aram Ilyich Khachaturian

I would like to organize by Last Name, First Name Middle Name (or initial):

Piatti, Alfredo C.
Dvorak, Antonin
Vivaldi, Antonio
Khachaturian, Aram Ilyich

Thanks very much!

[AliGW]

Is this a TRULY representative sample? Is the last word in the source data always the surname? Are there any variations?

[AlKey]

Try this
Enter formula in B1 and copy down

Please Login or Register  to view this content.

v	A	B
1	Alfredo C. Piatti*	Piatti*, Alfredo C.
2	Antonin Dvorak*	Dvorak*, Antonin
3	Antonio Vivaldi*	Vivaldi*, Antonio
4	Aram Ilyich Khachaturian	Khachaturian, Aram Ilyich

There is a char 160 in these strings
here is an adjusted formula

Please Login or Register  to view this content.

v	A	B
1	Alfredo C. Piatti*	Piatti, Alfredo C.
2	Antonin Dvorak*	Dvorak, Antonin
3	Antonio Vivaldi*	Vivaldi, Antonio
4	Aram Ilyich Khachaturian	Khachaturian, Aram Ilyich

[blawless]

Thank You AliGW.
Yes the last word is always the surname with no variations. Other examples:

Ludwig van Beethoven
Luigi Boccherini
Max Bruch
Max Bruch
Mazas Jacques
Nicolo Paganini
Pablo de Sarasate
Peter Ilyich Tchaikovsky
Pierre Gavinies
Pierre Rode
Richard Hofmann
Robert Wohlfahrt
Rodolphe Kreutzer
Sebastian Lee

[AliGW]

You may not have spotted that AlKey gave you a solution above in post #3.

[blawless]

Thank You AlKey,
This seems to work only for the 1st listing Alfredo C. Piatti. The others show Comma Space Full Name. Here is what I see:

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(L3," ",REPT(" ",99)),99))&", "&SUBSTITUTE(L3,TRIM(RIGHT(SUBSTITUTE(L3," ",REPT(" ",99)),99)),""),CHAR(160),"")

Composers.JPG

[AlKey]

It works fine for me

Please Login or Register  to view this content.

v	A	B
1	Name	Last, First
2	Ludwig van Beethoven*	Beethoven, Ludwig van
3	Luigi Boccherini*	Boccherini, Luigi
4	Max Bruch*	Bruch, Max
5	Max Bruch*	Bruch, Max
6	Mazas Jacques	Jacques, Mazas
7	Nicolo Paganini*	Paganini, Nicolo
8	Pablo de Sarasate*	Sarasate, Pablo de
9	Peter Ilyich Tchaikovsky*	Tchaikovsky, Peter Ilyich
10	Pierre Gavinies	Gavinies, Pierre
11	Pierre Rode*	Rode, Pierre
12	Richard Hofmann	Hofmann, Richard
13	Robert Wohlfahrt	Wohlfahrt, Robert
14	Rodolphe Kreutzer*	Kreutzer, Rodolphe
15	Sebastian Lee	Lee, Sebastian
16	Alfredo C. Piatti*	Piatti, Alfredo C.
17	Antonin Dvorak*	Dvorak, Antonin
18	Antonio Vivaldi*	Vivaldi, Antonio
19	Aram Ilyich Khachaturian	Khachaturian, Aram Ilyich

[AliGW]

Where did the asterisks come form?

[blawless]

My data does not have asterisks. Is this something I need to add?

[blawless]

There must be something strange with my data, because your formula works when I type in the full name but not when I use in my spreadsheet or copy and paste from my spreadsheet to another. Any suggestion on this? Thanks very much for your help!

[AlKey]

My data does not have asterisks. Is this something I need to add?

The asterisks come from the dirty data. Some spaces are actually char(160),nonbreaking space character (Unicode value of 160).
The formula I provided will remove this character.

[blawless]

Thank You. I am baffled that this works only when I type the names into a new column.Composers2.JPG

[blawless]

It worked perfect in Google Sheets. I must have something going on with my Excel. Thank You so much for your help!

[Tony Valko]

Just a thought...

You have this name:

Aram Ilyich Khachaturian

And want it in this format:

Khachaturian, Aram Ilyich

So, that means Ilyich is the middle name.

What if you had a name like:

Eddie Van Halen

Van is not the middle name!

How can Excel distinguish that one is a middle name while the other is a 2 word last name?

[AliGW]

viz van Beethoven up above in post #7!