9 Ways to Separate First and Last Names in Microsoft Excel

13 August 2024 Paolo Bergamaschi Excel 0
Excel Microsoft
Excel Microsoft

Datasets containing people’s names will often have the first name and last name in a single cell, separated by a space. While this isn’t necessarily a problem, it can be more useful to have the first and last names in separate columns. This way, you can more easily perform further analysis on the data, such as sorting and filtering by last name alone.

1. Separate First and Last Names with Text to Columns

When you need to split first and last names in your data on an occasional basis, “Text to Columns” should be your go-to method. The “Text to Columns” function will allow you to split the names into a new range or replace your current data. Whichever you prefer.

This is most suited for one-off use cases, as you will need to run the text to columns split again for every new name added to the list.

Here’s how to use “Text to Columns” to split first and last names:

  1. Select the range of names you want to split.
  2. Go to the “Data” tab.
  3. Click the “Convert to Columns” command in the “Data Tools” section.
  4. Select “Delimited” for your names to be split.
  5. Deselect the default “Tab” and select “Space.”
  6. Select a cell for the “Destination.”
  7. Click “Finish.”

Excel will split the names based on the space separating them and place the results in your desired location.

2. 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 beginning or end of any text value. You can use these in conjunction with the “FIND” function to get the position of the space character and the “LEN” function to get the total number of characters in the name.

Get the First Name

= LEFT ( B3, FIND ( " ", B3 ) - 1 )

The formula above will extract the first name from the full name description in cell B3. The “FIND” function will find the position of the first space character. When you subtract 1 from this result, you get the position of the last character of the first name. The “LEFT” function then uses this value to return these first characters from the name.

Get the Last Name

= RIGHT ( B3, LEN ( B3 ) - FIND ( " ", B3 ) )

The formula above will get the last name from the full name description in cell B3. The “LEN” function gets the total character count from the full name. The “FIND” function gets the position of the space character that separates the first and last names. When you subtract the total number of name characters from the space character position, you get the number of characters in the last name. This information is then used in the “RIGHT” function to extract the last name.

3. Separate First and Last Names with FILTERXML Function

A clever way to split text based on the space character is to use the “FILTERXML” function. This is intended to allow you to extract specific parts of XML data based on the node name.

But you can use it to extract names by first constructing the XML data from your name.

= TRANSPOSE ( FILTERXML ( "<t><s>" & SUBSTITUTE ( B3," ","</s><s>")&"</s></t>","//s"))

The formula above will split the full name in cell B3 into first and last name.

The “SUBSTITUTE” function is used to replace the space with text like that above. This creates an XML data structure, and you can use the “FILTERXML” function to extract each of the names.

Get Only the First Name

= FILTERXML ( "<t><s>" & SUBSTITUTE ( B3, " ", "</s><s>" ) & "</s></t>", "//s[1]" )

The previous version of the FILTERXML formula will return only the first name. The filter `//s[1]` will return only the first item in an s tag.

Get Only the Last Name

= FILTERXML ( "<t><s>" & SUBSTITUTE ( B3, " ", "</s><s>" ) & "</s></t>", "//s[2]" )

Similarly, the previous formula uses the filter `//s[2]` to return the second item in an s tag, which is the last name.

4. Separate First and Last Names with TEXTSPLIT Function

If you have a more recent version of Excel, you can skip the complex formula solutions because there’s a dedicated function for splitting text.

You can use the “TEXTSPLIT” function to split a text value based on a delimiter character.

This means that you can use the space character as the delimiter to separate the first and last name.

= TEXTSPLIT ( B3, " " )

The formula above will split the name in cell B3 based on the space character. It’s about as straightforward as it gets!

5. 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 split your names when you provide example results.

Here’s how it works:

  1. Type in a few first names in the cells to the right of the corresponding full name.
  2. This should trigger “Flash Fill,” and you’ll see the suggested results in light gray. Press “Enter” to accept these results and populate the cells.
  3. If “Flash Fill” doesn’t activate automatically after the first two or three entries, you’ll need to trigger it manually.
  4. Select the first empty cell below your first name examples.
  5. Go to the “Data” tab and click the “Flash Fill” command in the “Data Tools” section.
  6. Repeat the process in the next column to get the last names.

6. Separate First and Last Names with Power Query

Power Query is an amazing tool for transforming your data in pretty much any way you can imagine. Splitting your names is a simple task for Power Query!

Power Query will definitely be the way to go for your task of getting first and last names into separate cells if you want a repeatable process for importing and transforming your data into Excel.

Before using Power Query, you’ll need to add your names data to an Excel Table.

Here’s how to use Power Query to split names in your table:

  1. Go to the “Data” tab.
  2. Click the “From Table/Range” command.
  3. This will open the Power Query editor.
  4. Right-click the header of the column containing the names you want to split and choose “Split Column.”
  5. Choose the “By Delimiter” option from the submenu.
  6. Select “Space” from the “Select or enter delimiter” dropdown.
  7. Hit the “OK” button.

You will now have two new columns with the first and last names split.

7. Separate First and Last Names with Power Pivot

If you are analyzing data that includes a full name, you may want to split the names using Power Pivot.

Power Pivot’s data model can hold a massive number of rows in memory, so it’s perfect for working with any enormous dataset.

After loading your dataset into the data model, you can add additional columns with calculations using the DAX formula language.

The DAX formula language has many functions that are common with Excel, and in this case, the functions used will be exactly the same as the text functions seen previously.

You can add your data to the data model. Select a cell within the table that contains your names and go to the “Power Pivot” tab, then click the “Add to Data Model” option.

This will open the Power Pivot editor with your table loaded into the model.

You will be able to add new columns by clicking the “Add Column” header to the right of your data.

= LEFT ( Names[Full Name], FIND ( " ", Names[Full Name] ) - 1 )

The formula above will get the first name for the entire column.

= RIGHT ( Names[Full Name], LEN ( Names[Full Name] ) - FIND ( " ", Names[Full Name] ) )

The formula above will return the last name for the entire column.

You’ll now be able to use these calculated columns in a Pivot Table.

8. Separate First and Last Names with VBA

Perhaps you need to split names often and want a one-click solution for it.

VBA could 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 your Quick Access Toolbar so it’s always available to use with just one click.

Here’s how:

  1. Go to the “Developer” tab and click the “Visual Basic” command.
  2. Create a new module in the Visual Basic editor and add the following code:
Sub SplitNames()
Dim rng As Range
Dim arrNames() As String
Dim colCount As Integer
colCount = Selection.Columns.Count
If colCount <> 1 Then
MsgBox ("Select a single column!")
End
End If
For Each rng In Selection
arrNames = Split(rng.Value, " ")
rng.Offset(0, 1).Value = arrNames(0)
rng.Offset(0, 2).Value = arrNames(1)
Next rng
End Sub

This code first checks that you have selected only a single column range. If you have selected multiple columns, it will warn you to select a single column and 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 selecting your names and running the code, you will have the first and last name split in Excel.

9. Separate First and Last Names with Office Scripts

If you primarily use Excel online, you’ll need to use Office Scripts to create custom code for splitting your names.

You can create an Office script like this:

  1. Go to the “Automate” tab and click the “New Script” command to open the “Code Editor” for Office Scripts.
  2. Add the following code to the editor: 
    function main(workbook: ExcelScript.Workbook) {
//get selected range
let rng = workbook.getSelectedRange();
let rows = rng.getRowCount();
let cols = rng.getColumnCount();
if (cols != 1) {
return;
};
//loop through selected cells
for (let i = 0; i < rows; i++) {
for (let j = 0; j < cols; j++) {
//split name based on space
let txtName = rng.getCell(i, j).getValue().toString();
let arrName = txtName.split(" ");
//enter names in adjacent cells
rng.getCell(i, j + 1).setValue(arrName[0]);
rng.getCell(i, j + 2).setValue(arrName[1]);
};
};
};

Add the code to the “Code Editor” and hit the “Save” button.

  1. You can now select a single column range of names in your Excel sheet and hit the “Run” button.
  2. The code will check if your selection is a single column and stop the code’s execution if it isn’t.

Otherwise, the code will proceed and loop through your selected cells to split the names. The first and last names are then entered into the columns to the right of your selection.

Conclusion

Many datasets will include a name field. At times this will be provided as a full name in a single cell, and you’ll need to split the name into first and last name components. “Text to Column” or “Flash Fill” will solve the problem in a few easy steps and are perfect for those one-off uses.

A formula solution represents a more dynamic solution, better suited when your source dataset will be adding or modifying names. The formula you’ll use will depend on the functions available in your version of Excel. For larger datasets, or for external datasets that need to be imported into Excel first, the Power Query or Power Pivot solution will work best. Have you ever needed to split names? How did you solve it? Let me know in the comments below!

Pubblicato in
13 August 2024 Paolo Bergamaschi Excel 0

Se vuoi rimanere aggiornato su 9 Ways to Separate First and Last Names in Microsoft Excel iscriviti alla nostra newsletter settimanale

Be the first to comment

Leave a Reply

Your email address will not be published.


*