fbpx

Split First & Last Name Using Power Query

Sometimes, when you have a data table which includes full names of candidates in one column and you want to separate the full name into first name and surname in different columns.

Split First & Last Name With Power Query

This is a common task, but most people may use complex formulas to perform this task and waste their valuable time. With Power Query, you can split the full name i.e.First name in one column and Surname in the second column within a few clicks. Here is an example to understand this concept.

Step 1: Select the column you want to split

Here, the Name column of the data table contains both name and surname which we want to separate out. To do this, we first need to select the ‘Name’ column as shown in the picture below.

BEFORE SPLITTING COLUMNS

Step 2: Split Column by Delimiter

Once you select the column, you can carry out the below steps to split the column-

Transform > Split Column > By Delimiter

SPLIT COLUMN BY DELIMITER

Where a delimiter is a sequence of one or more characters used to specify the boundary between separate texts in a data. It can be any character like bracket, comma or simply space.

Step 3: Select the Delimiter

After you click on the Split Column option, a Split Column window gets open which helps you to select the delimiter of your choice from the drop-down list. Here, we have chosen the ‘space’ delimiter because our data is separated by space. Also the split option ‘Each occurrence of the delimiter’is selected by default.

SPLIT COLUMN WINDOW

When you click on OK, you will get the expected result with names and surnames separated as shown in below picture.

AFTER SPLITTING COLUMNS

Step 4Rename headers of the Split Columns

The split columns are named as Name.1, Name.2 etc. To rename the column headers, just double click on the header name and edit the name as you wish. We have renamed the column headers as Name and Surname respectively as shown below.

AFTER RENAMING COLUMN HEADERS OF SPLIT COLUMNS

You can learn more : 

Leave a Reply