Pinterest Pixel

Split First & Last Name Using Power Query

There are times when you receive a data set of employee full names in one column and... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

Split First & Last Name Using Power Query | MyExcelOnline Split First & Last Name Using Power Query | MyExcelOnline

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…

Split First & Last Name Using Power Query | MyExcelOnline
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

Split First & Last Name Using Power Query

 

STEP 2: Go to Power Query > From Table

Split First & Last Name Using Power Query

 

STEP 3: This will open up the Power Query Editor.  Within here you need to select Home > Split Columns > By Delimiter

Split First & Last Name Using Power Query

 

STEP 4: This will open up the Split Column by Delimiter dialogue box.

See also  Advanced Editor In Power Query

In the drop down box under Select or enter delimiter you need to choose Space

Split First & Last Name Using Power Query

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 

Split First & Last Name Using Power Query

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

Split First & Last Name Using Power Query

 

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

Split First & Last Name Using Power Query

You can amend the column headings and here you have your full name split into separate columns…Easy hey!

Split First & Last Name Using Power Query

 

PIVOT BANNER

If you like this Excel tip, please share it
Split First & Last Name Using Power Query | MyExcelOnline Split First & Last Name Using Power Query | MyExcelOnline
See also  50 Things You Can Do With Excel Power Query
Founder & Chief Inspirational Officer at MyExcelOnline.com

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.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...