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 WORKBOOK

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

HELPFUL RESOURCE:

PIVOT BANNER

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Duplicate Columns Using Power Query or Get & ... Power Query (in Excel 2010 & 2013) or Get & Transform (in Excel 2016) lets you perform a series of steps to transform your Excel data. One of the steps it allows you to do is to duplicate columns easily. This is helpful when you have columns that you want to duplica...
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 Replay BONUS VIDEO:   Consolidate Multiple Sheets & Excel Tables In this training you are going to learn the following:C...
How To Install Power Query in Excel 2010   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. CLICK TO SEE THE TUTORIAL ON HOW TO INSTALL POWER QUERY IN EXCEL 2013   So what is Power Query? We...
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...