Surname Sort (with a twist)

deepheat101

Board Regular
Joined
Jul 26, 2006
Messages
138
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..

Code:
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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
 

Forum statistics

Threads
1,144,582
Messages
5,725,110
Members
422,590
Latest member
Mikeyyy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top