Tip of the week # 620
31 August 2020

Split first name and last name into separate cells or combine first and last name in one cell (Microsoft Excel) *

Have you ever had the need to split a cell containing the full name of a person into a separate first name and last name cell? Perhaps you downloaded information from other software and you ended up with data in one cell that you would prefer to have in two cells?

If you are still using Excel 2010 you can use the Text to Columns option on the Data tab, as described in tip 225. But if you've upgraded to Excel 2013 or later you can use the Flash Fill functionality from the Auto Fill Options button that pops up when you've used the Auto Fill feature. (More about Auto Fill at the bottom of this tip under Related tips.)

But there is an even quicker way. OK, not a very intuitive one, but it's so good I will remember!

Here's how:

  1. Open FlashFill.xlsx to understand this example
  2. Click in cell B2.
  3. Type Anne.
  4. Press ENTER.
  5. Press CTRL + E.

You can also use it the other way around, i.e. turn two separate columns containing first name and last name into a full name. Much simpler than the CONCATENATE Function or the formula such as =A2&" "&B2.

With thanks to George for this week's tip inspiration!

Related tips

  1. Split first name and last name into separate cells - tip_225.php
  2. Move or copy cells using drag and drop - tip_530.php
  3. Stop AutoFill - tip_370.php
  4. Quickly increase a list of numbers by one - tip_258.php
  5. Quickly repeating a formula down a column - tip_40.html
  6. Quickly enter a series of weekday - tip_49.html


* Unless stated otherwise, these tips were written for Microsoft Office 2010.