How to Split Cells and Text in Excel with Power Query

Bottom Line: Learn how to split text into multiple cells or columns with Power Query. Split words or names that are separated by a space, symbol, or other character.

Skill Level: Intermediate

Watch the Tutorial

Download the Excel File

Follow along using the workbook from the video by downloading this file:

Split Text into Separate Columns

If you have text in a cell that is separated by a space, comma, or other character, and you'd like those components split into their own separate cells, you can use Power Query to create columns for each component.

A common example is separating the first name from the last name for a column that has both.

There are lots of ways to accomplish this task, including Text to Columns and Text to Cells, but we're going to use Power Query. That's because it allows us to automate this action for any changes or additions made to the source table. So anytime new names come in, we can refresh our query and have immediately updated columns.

Connect to a Data Source

To get started, select any cell in your table, and go to the Data tab (Power Query tab for older versions of Excel). Then click on the From Table/Range button.

That will open up the Power Query Editor, which shows a preview of our data. For my example, I want to keep a copy of the Full Name column in addition to having first and last name columns. So before I split my column, I can duplicate it by right-clicking on the column and selecting Duplicate Column.

Splitting the Column

With the column you want to split selected, go to the Home tab. Then select By Delimiter in the Split Column menu.

A delimiter is a blank space, comma, or other character/symbol that indicates the beginning/end of a character string, word, or data item. In our case, the first and last names are already separated by a space, so that will be our delimiter.

The Split Column By Delimiter Window will appear, allowing you to choose the delimiter you want to split by. Power Query will usually be able to recognize the common delimiter in your data and default to that option.

Because we are only splitting the first and last name and there is only one space in between, we can leave the Split at option where it is, on Each occurrence of the delimiter. Later, I will show you how to change that when there are more than one space in the data you want to split.

When you hit OK, you will see that your name has been split into two columns in Power Query.

You can rename the new columns (by double-clicking the column header) and move them as desired. You can also make other transformations if you want to filter or sort the data.

When you are done with your transformations, click the top half of the Close & Load button.

A new sheet will be created in the workbook that contains an Excel Table with results of the query.

You have now automated the process. I'll explain more about refreshing the query with new data at the end of this post.

Splitting into Multiple Columns

If your data has cells with three (or more) names instead of just two, you can split the columns twice if you want to separate the first and last names from the middle name(s).

The process is similar to above. However, for the first split, you want to choose the option that says Split at Right-most delimiter. That will pull the last name into its own column.

For the second split, you can repeat the process and this time choose the Left-most delimiter to pull out the first name only, especially if there is more than one middle name listed. If you are certain that there are not more than two names in the column, you could select the option that says Each occurrence of the delimiter, as we did before.

The first, middle, and last names are in their own respective columns, which you can rename and move around as before.

The word “null” just means that the cell will be blank when it is loaded to a worksheet.

Automatic Changes

Whenever additions, subtractions, or edits are made to the source data, the changes will be reflected in the output table as soon as it's refreshed. To refresh, just right-click on any cell in the table and choose the Refresh option. The keyboard shortcut to refresh is Alt + F5.

With Power Query, you can connect to sheets in other workbooks, CSV files, databases, and websites to pull in data. Then whenever the data from those sources changes, you can refresh the table and it will update.

Check out the video above to see an example of adding new data to the bottom of the table and refreshing the query.

Ways to Split Text

Here are the links to the other posts on ways to split text:

• How to Split Text in Cells Using Formulas
• Split Cells with Text to Columns in Excel
• How to Split Text in Cells with Flash Fill in Excel
• Split by Delimiter into Rows (and Columns) with Power Query

Conclusion

For more information on Power Query I suggest you take a look at these tutorials:

• Power Query Overview: An Introduction to Excel’s Most Powerful Data Tool
• The Complete Guide to Installing Power Query

Or sign up for my easy-to-follow webinar, Free Excel Training on The Modern Power Tools.

I hope this explanation for extracting first and last names has been helpful for you. If you have any questions or remarks, fell free to leave them in the comments below.