Today I've been working in a small (just 97 lines of code) Excel VBA macro that can be somewhat useful. This utility allows you to split up columns of data that contain Names and Surnames mixed, so that you get two new columns with this elements separated.

It is able to correctly split names in several formats:

• Name and Surname. (for example: Zachery Barr)
• Name Initial Surname. (Stewart H. Shepherd)
• Two Names and a Surname (José Manuel Alarcón)
• Two Names and several Surnames (José Manuel Alarcón Aguín)

Download Name and LastName Splitter.xls (54 KB)

In order to use it you must enable the support for macros everytime you open it in Excel:

Press "Options", and select the "Enable this content" option:

Now just copy and paste your mixed data in any column in any sheet of this spreadsheet.

It's important that you select the range of cells you need to be processed for splitting. If you don't make any selection only the current cell will be processed.

Now go to the "View" ribbon in Excel 2007 (or Tools·Macros in previous versions) and press the "Macros" button or press ALT+F8, as it's shown below:

In the dialog that appears execute the macro "SplitCurrentColumn":

Now you will get another sheet named "Split" with two columns. In the first one you'll have the names and in the second the Lastnames.

You can process as many data columns as you want but take into account that the "Split" sheet will be emptied each time you execute the macro, so save your new data first. You cannot process data in the "Split" sheet which holds the results.

In the download I've included a sheet with sample data for you to try (300 randomly generated names).

Hope this helps!