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

Watch on YouTube & Subscribe to our Channel

Download the Excel File

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

Excel File Icon 2021 xlsx Split-Names-with-Power-Query.xlsx

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.

Split text in cells into separate columns

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.

From Table Range on Data Tab

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.

Select Duplicate Column on right click menu

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.

Split Column by Delimiter

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.

Split Column by Delimiter window

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.

Split names into first and last

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.

Close and Load Button

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

Output table with split columns for first and last names

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.

split at right-most delimiter

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.

Names split into three columns

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.

right click to refresh table

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.

Conclusion

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

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.

    • Hi HG,
      Sorry about that. The video is available now. Great point about Flash Fill. I add that to our list of follow-up posts with alternate solutions. Thanks! ๐Ÿ™‚

  • Hi John, what if the database is mixed with just normal name like Kelly Smith and some names are like this? Jane Le Fevre, Miguel de Leon
    How would you run the power query?

    Thanks

    • Hi Gina,
      Great question!

      In this case, it looks like some of the names have two last names.

      You could use the technique I showed at the end of the video for splitting multiple columns but go left-to-right instead of right-to-left.

      You would first split at the Left most delimiter to give you a column of first names. The second column would include the last name(s). Where there are two last names it would contain both last names in that second column.

      I hope that helps. Thanks again and have a nice day! ๐Ÿ™‚

  • How would you handle a list of names that can be any combination of first/middle/last/suffix? And last names that may be double-barrelled, like Andrew Lloyd Webber? Or is this just a case of some manual labor.
    It would be amazing to always have a well-formatted list, but even exports from third-party databases seem to rarely be written in a way that facilitates clean data manipulation.

    • Hi Steven,
      Great question!

      I just replied to Gina in the comment above with a solution for double-barrelled last names, using a technique to split from left to right.

      However, that won’t cover scenarios where there are additional names, two first names, a middle name, suffix, etc.

      It’s going to be challenging to account for all of those scenarios.

      Lloyd Webber is a great example of two last names. But how would any logical statement be able to figure that out without prior knowledge or a database to reference. This is where machine learning might be helpful.

      However, if it is a list of names that doesn’t change too often, like a list of employee names, you could build a lookup table and use the merge feature of Power Query to do lookups to that table.

      The lookup table might take some manual effort to get everything correct the first time, but you could reuse it in any data set you import that contains that list of names.

      The lookup table would have a column with the full name. Then additional columns for first and last. You could also have columns for middle, suffix, etc., if needed.

      You would then do a merge (lookup/join) on any lists of full names in new data sets to that full name column in the lookup table. Then you can expand the additional name columns to bring them into the new data set.

      I realize that isn’t going to work in all scenarios as different databases always seem to have different versions of the names. But if you only work with a few databases then you could add columns with those full name varieties to the lookup table and use them for different database merges.

      Another option might be to use the fuzzy match/join feature of Power Query.

      I don’t know that there is one perfect solution, but hopefully that gives you some ideas on how to save time with this task if it’s something you do frequently or periodically.

      Thanks! ๐Ÿ™‚

  • Hi John, Do you think in the Power Query instead of creating a duplicate column we can also go to “Add Column” “Extract” and perform text before, after and between delimiters to get First, Middle and Last Name split into separate columns. Thoughts?

  • Hello Jon,
    Thanks for the data cleanup video by using Power Query. I attended you courses last year but didn`t get the chance to use all the functions I`ve learned. So I almost forgot how to….
    Getting a short brush up from time to time really helps me. Thanks again!
    Jan

  • Hello John
    The “split up kids into two groups for a fielding exercise” is a challenge not only for 4 to 6-year-old boys, but also for Excel. Can You create a post, how to split a range of names (different count) into two groups (two columns) using power query? Thank You

  • Hi Jon,
    I’m dealing with a “live” situation like this now and in my case, the salutation can be one of: “Mr.”, “Mrs.”, “Ms.”, “Mr. and Mrs.”, “Dr. and Mrs.”, “Mr. and Dr.” etc.
    (The quotes are there to only to make it clearer, but, do not appear in the text.)
    Surnames can be more than one word (e.g. Lloyd Webber)
    First names can be 2 words.
    My job is to have 3 cells filled: Salutation, First Name, Last Name

    Can you please suggest a general approach to solve this?

    As an aside, I found a VBA Function which scans from right to left to find the first occurrence of a given character string.

  • Hi Jon,
    what version of Excel has the option for ‘Power Queries’ – I can’t see it in Excel 2016?

  • Hi John,
    So I have used Split Cells and Text in Excel with Power Query to split information to 3 columns, which is great btw!!
    My issue now is, the information in the cell thatcontains birthdate.
    I got the birthdate extracted from that one cell and when I copy and paste the birthdate column (which has slashes 12/03/1993) to another column (paste values only) the formula is still there. I used the text to columns and formula =text(col row, “000000”) but the I can’t get the birthdate to look like this 12031993.

    My question is, is it bec of Power Query (hidden formulas)?

    Cheers,
    Gina

  • Search
    Generic filters
    Exact matches only

    JOIN US & LEARN EXCEL

    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    Excel Shortcuts List

    keyboard shortcuts list banner

    Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

    Excel Shortcuts List

    Join Our Weekly Newsletter

    The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

    Join Our Free Newsletter

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

    MVP_Horizontal_BlueOnly

    Free Excel Training Webinar Modern Power Tools

    >