Datasets with people’s names usually have the first and last name in one cell, designated by a space character. Although this isn’t necessarily bad, it can be more helpful to have the first and last names in different columns. This way you can more easily perform further analysis on your data such as sorting and filtering based on just the last name. This post will show you all the ways to extract the first and last names into different cells in Excel!
Separate First and Last Names with Text to Columns
When you need to split the first and last names into your data on a one-off basis, Text to Columns should be your go-to method. The Text to Columns feature will allow you to split the names into a new range or replace the current data. Whichever you prefer. This is best for one-time use cases since you will need to perform the text-to-column split again on any new names added to the list. Here’s how to use text to column to separate the first and last names. This can only be a single column range. If you try to proceed with a selection of more than one column you will get a pop warning telling you that Microsoft Excel can convert only one column at a time. This will open the Convert Text to Columns Wizard which will walk you through the steps to split your names. When you select the Space option you should see a black line appear that separates your names in the Data preview section of the menu. The Destination is the location in your sheet where Excel will place the first and last names, so this will need to be empty. Select a single cell that will be the top leftmost cell of the range where Excel will place all the names. Excel will warn you if you try and use a destination where there is already data in place and ask you if you want to replace it. Excel will split the names based on the space character that separates them and place the results in your desired location.
Separate First and Last Names with Text Formula
Another way to get the first and last names from your set of full names is to use a combination of text functions to parse each name. You can use the LEFT and RIGHT functions to get the desired number of characters from the start or end of any text value. You can use these in combination with the FIND function to get the location of the space character and the LEN function to get the total number of characters in the name.
Get the First Name
The above formula will extract the first name from the full name in cell B3. The FIND function will find the place of the first space character. When you subtract 1 from this result you will get the place of the last character from the first name. The LEFT function then uses this value to return these first characters from the name.
Get the Last Name
The above formula will get the last name from the full name in cell B3. The LEN function gets the total character count from the full name. The FIND function gets the place of the space character that separates the first and last names. When you subtract the total length of the name by the place of the space character you get the character count of the last name. This is then used in the RIGHT function to extract the last name.
Separate First and Last Names with FILTERXML Function
A sneaky way to split text based on the space character is to use the FILTERXML function. This is meant to allow you to extract certain parts of XML data based on the node name. But you can use it to extract the names by first creating XML data from your name. The above formula will split the full name in cell B3 into first and last names. The SUBSTITUTE function is used to replace the space with some text like the above. The & is then used to concatenate text onto the start and end of the names so it results in something like the above. This is now an XML data structure and you can use the FILTERXML function to extract each of the names. The above //s filter will extract each of the items enclosed in the s-tags which are now the first and last names. This results in a column of names and you can use the TRANSPOSE function to turn this into a row of names.
Get the First Name Only
The above use of the FILTERXML function will split each name and result in an array of two cells. But you can modify this formula a bit to only get the first name. The above version of the FILTERXML formula will only return the first name. The //s[1] filter will return only the 1st item in an s-tag.
Get the Last Name Only
Similarly, the above formula uses the filter //s[2] to return the 2nd item in an s-tag which is the last name.
Separate First and Last Names with TEXTSPLIT Function
If you have a more recent version of Excel, then you can avoid the complex formula solutions because there is a dedicated function for splitting text. You can use the TEXTSPLIT function to split a text value based on a delimiter character. This means you can use the space character as the delimiter to separate the first and last names, The above formula will split the name in cell B3 based on the space character. It’s about as simple as you can get!
Separate First and Last Names with Flash Fill
Flash Fill is a handy way to transform your data based on examples. You provide a few examples of the results you want in the adjacent column and Flash Fill will determine the pattern and fill in the rest. This can easily separate your names when you provide the example results. You can check out this guide to flash fill in Excel for more details on this versatile data tool. Type a few first names alone into the cells to the right of the corresponding full name. This should trigger Flash Fill and you will see in light gray the suggested results. Press Enter to accept these results and place them into the cells. If Flash Fill doesn’t trigger automatically after the first couple of entries, then you might need to manually trigger it. Select the first empty cell below your example first names. Then go to the Data tab and click on the Flash Fill command found in the Data Tools section. 💡 Tip: You can also use the keyboard shortcut Ctrl + E to use flash fill. Now you can repeat the process in the next column to obtain the last names. 📝 Note: Just note these values are static and won’t update if you update the full name column. You will need to rerun the Flash Fill command to update them.
Separate First and Last Names with Power Query
Power Query is an amazing tool for transforming your data in just about any way imaginable. Splitting your names is a simple task for Power Query! Power Query will definitely be the way to go with your task of getting first and last names into separate cells if you want a repeatable process to import and transform your data in Excel. Before using Power Query, you will need to add your name data to an Excel Table. Now you can use Power Query to split the names in your table. This will open the Power Query editor. This will open the Split Column by Delimiter menu where you can select what delimiter to split the data on. That’s it. The names are all split and you should have two new columns with the first and last names. 💡 Tip: Double-click on the new column headings to rename them. Now you will be able to load the transformed data back into Excel. This will open the Import Data menu where you can choose where you want to load the data. Your separated names will then load to your selected location. 💡 Tip: If you edit, add, or remove names from the source list, you can update the separated names easily to reflect the changes. Go to the Data tab and click the Refresh All command.
Separate First and Last Names with Power Pivot
If you are analyzing data that includes a full name, then you might want to split the names using Power Pivot. The Power Pivot data model can hold a large number of rows in memory, so it’s perfect for working with any huge dataset. After you load your dataset to the data model, you can add more columns with calculations using the DAX formula language. The DAX formula language has a lot of common functions with Excel and in this case, the functions used will be the exact same as the text functions seen earlier. You can add your data to the data model. Select a cell inside the table that contains your names and go to the Power Pivot tab then click on the Add to Data Model option. This will open the Power Pivot editor with the table load to the model. You’ll be able to new columns by clicking on the Add Column heading to the right of your data. The above formula will get the first name for the entire column. The above formula will return the last name for the entire column. Now you will be able to use these calculated columns in a Pivot Table. Go to the Home tab of the Power Pivot Add-In and click on the PivotTable command and choose the PivotTable option from the menu. These new first and last name calculated columns will then be available as fields from your PivotTable Fields menu.
Separate First and Last Names with VBA
Perhaps you need to separate names often and want a one-click solution for this. VBA might be the best solution for you. You can add a macro to your workbook that will split the names into adjacent columns. This can be run by adding the macro to the Quick Access Toolbar so it’s always available to use in one click. Go to the Developer tab and click on the Visual Basic command. You can check out this post for details on enabling the Developer tab in the ribbon. Alternatively, you can press Alt + F11 to open the visual basic editor without the Developer tab. This will open the visual basic editor. Now you can create a new module in the editor to add your code into. Go to the Insert menu of the visual basic editor and select the Module option. Add the above code to your module in the visual basic editor. This code first checks that you have only selected a range with a single column. If you have selected multiple columns it will warn you to select a single column and then stop execution. When a single column is selected, the code will loop through the selected range and split each cell based on the space character. The first and second parts of the split names are entered into the cells directly to the right of your original selection. After you select your names and run the code, you’ll have the split first and last names in Excel.
Separate First and Last Names with Office Scripts
If you mostly use Excel online, then you will need to use Office Scripts to create custom code to separate your names. A similar Office Script can be created. Go to the Automate tab and click on the New Script command to open the Code Editor for Office Scripts. Add the above code to the Code Editor and press the Save button. Now you can select a single column range of names in your Excel sheet and press the Run button. The code will check if your selection is a single column and stop running the code if it is. Otherwise, the code will continue and loop through your selected cell to split the names. The first and last names are added in the columns to the right of your selection.
Conclusions
A lot of datasets will include a name field. Sometimes this will come as a full name in a single cell and you will need to separate the name into the first and last name components. Text to column or Flash Fill will get the job done with a few simple steps and is perfect for those one-time uses. A formula solution represents a more dynamic solution that is better suited when the source set of data will get names added or edited. Which formula you use will depend on what functions are available in your version of Excel. For larger datasets or external datasets that need to be imported to Excel first, the Power Query or Power Pivot solution will work best. Have you come across the need to separate names? How did you get it done? Let me know in the comments below!
