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


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




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