Parse Excel Column, First Name (<optional>)Middle, Last name into 3 columns?

Question

How do you parse Excel Column, First Name ()Middle, Last name into 3 columns?

Ivan P. Freely
Jordan Smith

How would you parse the above Excel column list of full names into 3 distinct columns with first, middle, last name when the middle name may not appear at all?

Answer 1

Not an elegant solution, but it works and does so reasonably quickly.

In Excel 2007, use the "Text in columns tool" on the Data ribon. Use the Space as a separator. The result will be

 A   | B     | C      | 
Ivan | P.    | Freely | 
John | Smith |        |

Then in cell E1, use the formula = A1. In cell F1, use the formula =IF(ISEMPTY(C1);"";B1). In cell G1, use the formula =IF(ISEMPTY(C1);B1;C1). Copy the formulas from E1, F1 and G1 into the rows below. Your separated data is in columns E, F and G.

This solution assumes that everybody only has exactly one or exactly zero middle names entered, and that if you have people with a patronymic (Nadezda Vasilevna Orehova) or people with more than one surname (Irene Garcia Cano), you want to treat the second substring in the same way as a middle name. If you have just a few exots, I'd recommend still going this way and then correcting them per hand. If you have lots of them, or too much data to examine manually, it is probably better to write a macro for the whole process.