Download Excel Workbook
There are times when you receive a data set of employee full names in one column and you want to separate the full name into first name and surname in separate columns.
This is a common task and most people may refer to complex formulas to do this and waste lots of time in the process.
With Power Query, you can split the full name that is in one cell into separate columns with just a few mouse clicks.
Here is how…
Download excel workbookSplit-Columns-First-Last-Name.xlsx
STEP 1: Click in your data and turn it into an Excel Table by pressing the shortcut CTRL+T or by going to Insert Table
STEP 2: Go to Power Query > From Table
STEP 3: This will open up the Power Query Editor. Within here you need to select Home > Split Columns > By Delimiter
STEP 4: This will open up the Split Column by Delimiter dialogue box.
In the drop down box under Select or enter delimiter you need to choose Space
STEP 5: For the Split option, you need to select the default At each occurrence of the delimiter (since there is only one space in each cell’s data) and press OK
This will split the full name into two separate columns, one for the first name called FULL NAME.1 and another for the surname called FULL NAME.2
STEP 6: Now all you need to do is press Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the split columns
You can amend the column headings and here you have your full name split into separate columns…Easy hey!
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.
