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…


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

excel table


STEP 2: Go to Power Query > From Table

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

split column by delimietr


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

space delimiter

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 at each occurrence

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 columns


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

close & load

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

new columns

Split Columns - First & Last Name



If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Fill Down Values Using Power Query Power Query lets you perform a series of steps to transform your Excel data.  One of the steps it allows you to take is to fill data down easily.You might be wondering when you might need to fill data down in your table.Let's suppose you have this set of data:A lo...
Install Power Query With Excel 2013 Power Query is a new add-in that was introduced in Excel 2013 by Microsoft and was that popular that they made it backward compatible with Excel 2010.Read the tutorial on how to install Power Query in Excel 2010 here So what is Power Query? Well you know when you get data f...
Unpivot Data Using Excel Power Query Power Query is a free add-in created by Microsoft for Excel 2010 (or later) and you can download and install it for Excel 2010 and 2013:Click to see tutorial on how to install Power Query in Excel 2013Click to see tutorial on how to install Power Query in Excel 2010In...
Protected: What Else Can Excel Pivot Tables Do?  The Pivot Table Webinar Replay & Xtreme Pivot Table Online Course is Closing its Doors in...Click Here To Watch The Webinar ReplayBONUS VIDEO:   Consolidate Multiple Sheets & Excel TablesIn this training you are going to learn the following:C...