Surname Sort (with a twist)

[deepheat101]

Greetings All,

I suspect that this one is going to be a little nasty, but here goes.

I have a list of French Artists in the format "First-Name Surname", and I need to rearrange to "Surname, First-Name". "So, where's the problem?", I hear you ask...

Well this is a sample of the names, along with the output that would be expected..

Original Name                             Formatted Name
=============                             ==============
Charles Le Brun                           Le Brun, Charles
Sebastien Bourdon                         Bourdon, Sebastien 
Robert Nanteuil                           Nanteuil, Robert 
Jean de Touyl                             de Touyl, Jean
Evrard d’Orleans                          d’Orleans, Evrard 
Jacquemart de Hesdin                      de Hesdin, Jacquemart 
Andres Marsal de Saxe                     de Saxe, Andres Marsal
Jean Clouet the Younger                   Clouet the Younger, Jean
Georges de La Tour                        de La Tour, Georges
Antoine Le Nain                           Le Nain, Antoine 
Gaspard Marsy II                          Marsy II, Gaspard 
Pierre Le Gros I                          Le Gros I, Pierre 
Jean-Baptiste de Champaigne               de Champaigne, Jean-Baptiste
Corneille Van Cleve                       Van Cleve, Corneille 
Pierre Etienne Monnot                     Monnot, Pierre Etienne
Jean de la Croix I                        de la Croix I, Jean
Pierre Le Gros II                         Le Gros II, Pierre 
Guillaume Coustou the Elder               Coustou the Elder, Guillaume
Charles La Croix de Marseille             de Marseille, Charles La Croix
Maurice Quentin de La Tour                de La Tour, Maurice Quentin
Louis Nicholas van Blarenberghe           van Blarenberghe, Louis Nicholas
Gabriel Jacques de Saint-Aubin            de Saint-Aubin, Gabriel Jacques
Jean-Simeon Rousseau de la Rottiere       de la Rottiere, Jean-Simeon Rousseau
Joe                                       Joe

Ordinarily, I would sort this list by hand, but the complete list easily runs into the thousands. (Who would have thought that the French have so many artists?!)

Any assistance is greatly appreciated.

Many Thanks in advance

 

[Damon Ostrander]

Hi deepheat101,

This is a common problem that occurs with English names as well (not to mention addresses, etc.), and especially when you consider the additional problem of prefixes and suffixes, like "Sir" (for one that has been knighted) to Esquire (for an attorney). This is why virtually all electronic forms use separate fields for prefix, first name, surname, suffix, etc.

But getting back to your problem, this is rather difficult as it is but this would be fairly straightforward it the rules governing parsing of the names were stated. For an example of parsing rules for western names, see

http://informationr.net/ir/9-4/paper192.html

It is clear that these rules don't quite satisfy your requirements. But if you could modify them such that they do (I suspect you would have to add more rules and exceptions), you could then repost your request with the rules and I think that you would find someone willing to implement the rules in Excel in short order.

Damon