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.

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

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

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

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

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

## Related Articles

**2 Easy Steps to Sort Alphanumeric Data in Excel****8 Ways to Arrange Numbers in Ascending Order in Excel Using Formula****4 Quick Ways to Create Custom Sort List in Excel**