How to Sort by Last Name in Excel

Sorting is ordering data in an increasing or decreasing order based on a linear connection between the data pieces. We can sort different data types in Excel, like names, numbers, and records. Sorting minimizes the consumed to look up data. We can sort people’s names by their first names. However, sometimes sorting them by last name might be necessary. For that reason, this article demonstrated five easy ways to sort by the last name in Excel.

Contents hide

📁 Download Excel File

Learn to Sort by Last Name in Excel with These 5 Distinctive Methods

1. Using Find & Replace Feature

2. Utilizing Text to Columns Feature

3. Applying Flash Fill Feature

4. Employing Formulas

4.1 Without Middle Names

4.2 With Middle Names

5. Using Power Query

📝 Takeaway from This Article

Conclusion

📁 Download Excel File

You can download the Excel file used for the demonstration from the link below.

Sort by Last Name.xlsx

Learn to Sort by Last Name in Excel with These 5 Distinctive Methods

Firstly, let us get acquainted with our dataset used throughout the article as an example. We have the names of some footballers, and we want to sort them by their last names. For that, we will extract their last name in the adjacent column. Hence, we will use this dataset to demonstrate five ways to sort by last name in Excel.

method 1

1. Using Find & Replace Feature

Excel’s Find and Replace feature looks for something in the spreadsheet, such as a specific number or text string. It can either find the search item for a future reference or swap it out for another object. This function can also include wildcard characters in search words such as question marks, tildes, asterisks, and numerals. It can also search by rows and columns, within comments or values, or within worksheets or entire workbooks. In this method, we will use this feature to sort our dataset by the last name in Excel. Let us see the steps,

⬇️⬇️ STEPS ⬇️⬇️

First, Copy the cells that you want to sort. We select the range B6:B10 and copy them by pressing Ctrl+C.

Then, Paste them into cell C6 by pressing Ctrl+V.

After that, select range C6:C10 and press Ctrl+H.

Then, a popup named Find and Replace will appear. Type “* ” in Find What, keep the Replace with option blank and press Replace All.

Press OK in the popup Microsoft Excel.

And click on Close.

Then select the cell range C6:C10 and go to the Data tab in the ribbon and select Sort from the Sort & Filter.

Select “Last Name” from Sort By option in the Sort dialog box and press OK.

Finally, we will get our sorted data.

method 2

2. Utilizing Text to Columns Feature

In Excel, the Text to Columns feature separates the text into different columns based on some defined or fixed width. We can use text to columns in two ways. One is to use a delimiter, in which we set a delimiter as an input, such as a comma space or a hyphen, and another is to use a fixed determined width to divide the text into neighboring columns. Now, we will show the steps to utilize Text to Columns to sort by last name in Excel.

⬇️⬇️ STEPS ⬇️⬇️

To begin with, select the range B6:B10.

Go to the Data tab and select Text to Column from the group Data Tools.

After that, from Convert Text to Column Wizard box select Delimited as the original data type and click on Next.

Then from delimiters click on Space and click on Next.

Next, from the column data type select the Do not import column(skip), enter C6 in the Destination, and click on Finish.

Finally, we will get the sorted data.

method 3

3. Applying Flash Fill Feature

Flash Fill is a tool that fills your data automatically when it detects a pattern. It can separate first and last names from a single column or combine first and last names from two different columns. Thus, we will use Flash Fill now to sort by last name. Let us see the steps.

⬇️⬇️ STEPS ⬇️⬇️

First, in cell C6, type the last name of cell B6. We typed “Ozil” here.

Then, do the same for cell C7.

After that, drag the plus or Double-click on it.

Click on Flash Fill from the AutoFill.

Finally, we will get our outcome.

📕 Read More: 5 Ways to Auto Sort When Data Is Entered in Excel

method 4

4. Employing Formulas

We can also sort our data by employing formulas. This method will demonstrate two formulas for sorting by the last name.

method 4.1

4.1 Without Middle Names

This section will use the same dataset consisting of only the first and last names. This time we are using RIGHT, LEN, and FIND functions. Let us see the steps and how this formula works.

⬇️⬇️ STEPS ⬇️⬇️

Firstly, select cell C6.

Then, insert the given formula.

=RIGHT(B6,LEN(B6)-FIND(” “,B6))

🔨 Formula Breakdown

Here, the formula inserted in C6 is

RIGHT(B6,LEN(B6)-FIND(” “,B6))

👉 LEN function returns the number of characters in a text string. So, LEN(B6) returns 10 as the text is “Mesut Ozil”.

👉 On the other hand, the FIND function locates a text string within another text string and returns the starting position number of the first text string from the second text string’s first character. So, FIND(” “, B6) finds the space in between the first and last name of cell B6 and returns 6, and LEN(B6)-FIND(” “,B6) returns 4.

👉 Lastly, the RIGHT function returns, depending on the number of characters you specify, the final character or characters in a text string. Thus, we have the output “Ozil” since these are the rightmost characters of cell B6.

At last, drag down the plus or double-click on the plus to Autofill the cells C7:C10 and the sorted data will look like this.

method 4.2

4.2 With Middle Names

In this section, we are using RIGHT, LEN, FIND and SUBSTITUTE functions. Since we are showing a formula that would work when the text contains a middle name or Mr./Ms. So, we are using a new dataset.

Now, let us go through the steps and how this formula works,

⬇️⬇️ STEPS ⬇️⬇️

If the name contains Mr/Ms or has any middle name, then select cell C6 and insert this formula.

=RIGHT(B6,LEN(B6)-FIND(“@”,SUBSTITUTE(B6,” “,”@”,LEN(B6)-LEN(SUBSTITUTE(B6,” “,””)))))

🔨 Formula Breakdown

Here, the formula inserted in C6 is

RIGHT(B6,LEN(B6)-FIND(“@”,SUBSTITUTE(B6,” “,”@”,LEN(B6)-LEN(SUBSTITUTE(B6,” “,””)))))

👉 SUBSTITUTE function substitutes new text for old text in a text string.SUBSTITUTE(B6,” “,””) substitutes all spaces in cell B6 by no space and returns “Mr.MesutOzil”.

👉 Then, the LEN function returns the number character, which is 12 in this case. So, LEN(B6)-LEN(SUBSTITUTE(B6,” “,””) returns 2.

👉 Then, SUBSTITUTE(B6,” “,”@”, LEN(B6)-LEN(SUBSTITUTE(B6,” “,””)) returns Mr. [email protected] and then the FIND function finds the @ in the outcome and returns 10 that is the number of characters up to @.

👉 Then, LEN(B6)-FIND(“@”,SUBSTITUTE(B6,” “,”@”,LEN(B6)-LEN(SUBSTITUTE(B6,” “,””)))) returns 4 and the RIGHT function then displays the last name “Ozil”.

At last, drag down the plus or double-click on the plus to Autofill the range C7:C10, and the sorted data will look like this.

📕 Read More: 6 Ways to Auto Sort When Data Changes in Excel

method 5

5. Using Power Query

In this last method, we will use Power Query to sort by the last name in Excel. Power Query (also known as Get & Transform in Excel) allows you to import or connect to external data and then modify that data in ways that match your needs, such as removing a column, changing a data type, or merging tables. The query can then be loaded into Excel to generate charts and reports. Let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

First, select the range B5:B10.

Go to the Data tab and select From table.

Then, Create Table popup will appear. Select My table has Headers and press OK.

After that, Power Query Editor window will appear.

Right-Click on the table and select the Duplicate column.

Then select the column “Name Copy” and click on the Split column and select By Delimiter.

Then Split Column By Delimiter named dialog box will appear and select “space” as a delimiter, split at “rightmost delimiter” and “none” from quote character and press OK.

After that, the column will split into two columns containing the first and last names. Right-click on the table containing the last name and select Ascending Order.

After that, Remove the middle column.

Then go to the Home tab of the editor and select Close and Load to.

Finally, the table containing sorted data will appear in the destination cells and we have changed the name of the second column from “Name. Copy 2” to “Last Name”.

📕 Read More: How to Sort Duplicates in Excel Easily

📝 Takeaway from This Article

📌 This article demonstrated five ways to sort by the last name in Excel.

📌 In the first method, we used Find and Replace to sort by the last name in Excel.

📌 Then, we utilized the Text to Column method for sorting in Excel by last name.

📌 After that, we used the Flash Fill option function to sort by the last name in Excel.

📌 Next, we demonstrated two formulas for sorting: if the name contains two parts and if there is a middle name or Mr/Ms in the preceding part.

📌 Finally, we showed the steps to apply Power Query to sort by the last name in Excel.

Conclusion

This article has demonstrated five ways to sort by the last name in excel. All these formulas are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.

For more guides like this, visit Excelden.com.

(Visited 105 times, 1 visits today)