Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
While you could make an incredible complicated worksheet function to do this, it'd be a lot easier in vba, especially if you never have more than one middle name. – Lance Roberts Jul 20 '10 at 17:21
up vote 3 down vote accepted

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.

share|improve this answer
Thanks for the help! Very clever solution! I had to use "," instead of ";" and the ISBLANK() function, but you are an Excel wizard! – MikeN Jul 20 '10 at 17:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .