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

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Inventory Your Files Using Power Query Power Query lets you perform a series of steps to transform your Excel data.One of the possible steps that surprised me was that I could list out the files in my computer! This is useful if you want to do some analysis on your file list (i.e. finding a file, checking whic...
Import Data from CSV Using Power Query or Get ... Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.But what if your data source is not in your Excel spreadsheet but located on your desktop?If it's inside a CSV file - Comma Separated Values which is denoted...
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...
Getting Started with M in Power Query  Power Query lets you perform a series of steps to transform your Excel data.There are times when we want to do things that are not built in the user interface. This is possible with Power Query's programming language, which is M.To start off, we will do a simple...