Clever Clogs tip # 709
28 July 2022


Split first name and double-barrelled surnames into separate cells (Microsoft Excel 2013 onwards) *

This week's tip is inspired by Sian Gardner who asked whether Flash Fill can be used to separate first and last names if there are double-barrelled surnames.

The short answer is: Yep!

Here's how:

  1. Open FlashFill.xlsx if you want to try out this example.
  2. Select cell C2.
  3. Type Brown.
  4. Press ENTER.
  5. Press CTRL + E. (You can also use the Flash Fill button on the Data tab in the Data Tools group. Or use AutoFill and select Flash Fill from the Auto Fill Options drop-down menu.)
  6. Fix Harvey Oswald. (Tip: select cell C3, press F2, Home and type Harvey followed by a space.)
  7. Press ENTER.

It even gets the hyphenated name correct. And if you don't like it you can undo the revision. But why would you?!

I didn't know this, Sian, so thanks for the question and for this week's tip inspiration.

Related tips

  1. Split first name and last name into separate cells or combine first and last name in one cell: tip_620.php
  2. Change case without using Formulas and Functions: tip_628.php
  3. Quickly increase a list of numbers by one: tip_258.php

This week's CleverClogsTipTime on LinkedIn

  1. Reset your browser's zoom level


* Unless stated otherwise this tip is written for Microsoft 365 desktop apps and Windows 10 users, but might also be useful in Office 2010, 2013 and 2016.