Weekly Computer Tip # 225
8 March 2008

Split first name and last name into separate cells (Microsoft Excel 2007)

When explaining Excel's "right" and "left" functions to separate information into another column, one of this week's course participants asked whether she could use this to split the first name and last name into separate cells. Well, unless everybody has a name with the same number of characters that wouldn't work, so I thought it would involve some clever VBA script or exporting to Word and convert the text to table, separating the text at the space. But then I spotted Excel's new wizard that will quickly separate cell content, such as first names and last names, into separate columns.

Here's how:

  1. Select the cell containing the first name and last name.
  2. On the Data tab, in the Data Tools group, click Text to Columns.
  3. Click on the Next button.
  4. Tick the Space box and click Next.
  5. Click on Finish.

OK, if somebody has a posh double-barrel name or the cell contains a middle name and/or initials, you might need to do a little bit of cleaning up (as it creates a third column) but it still saves a lot of time, I think. So I'd like to thank Wendy Griffin of Britain's most prestigious veterinary college for this week's inspiration!