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.


📁 Download Excel File

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


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.

How 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.

Using find & replace to sort by last name in excel

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

Find & replace to sort by last name in excel

  • 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.

Using Sort tool to sort by last name in excel

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

Sort by last name in Excel

  • Finally, we will get our sorted data.

Outcome of how to sort by last name in excel

📕 Read More: Sort Multiple Columns Independently of Each Other in Excel


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.

Selection of cells to sort by last name in excel

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

Using Text to Column to sort by last name in excel

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

using convert text to column to sort by last name in excel

  • 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.

Outcome of how to sort by last name in excel

📕 Read More: How to Sort Alphabetically in Excel with Multiple Columns


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.

Using flash fill to sort by last name in excel

  • 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))

Insert formula to sort by last name in excel

🔨 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.

Copy and paste formula to sort by last name in excel


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.

Dataset with middle name to sort by last name in excel

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,” “,””)))))

Insert formula to sort by last name in excel

🔨 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. Mesut@Ozil 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.

from table to sort by last name in excel

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

create table to sort by last name in excel

  • After that, Power Query Editor window will appear.

Power query editor to sort by last name in excel

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

duplicate column to sort by last name in excel

  • 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.

Set delimiter to sort by last name in excel

  • 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”.

 How to sort by last name in excel

📕 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.


Related Articles

(Visited 182 times, 1 visits today)
Priti

Priti

Hello! Welcome to my Profile. I am Priti. I'm now working and researching Microsoft Excel, and I'll share articles on it here. I am passionate about problem-solving, and I hope these articles will help you. Please leave a remark if you have any queries, and I will do my best to find an appropriate answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo