How Do You Alphabetize By Last Name In Excel?

In this commodity, you lot will learn how to sort names by concluding name in Excel and Google Sheets.

Assuming you take a starting time and last name together in 1 cell, at that place are several different means to extract the last name and sort the full name column by last name.

Sort by Last Name With Text to Columns

The first option to sort by last name is to extract the last name from the full name in a split up cavalcade using the text to columns feature in Excel. Say you have the post-obit list of names, containing only ane first proper noun and one surname separated past a space, in Column B:

1. Select the column that you want to sort (B in this example), and in the Ribbon, go to Data > Text to Columns.

2. In the Convert Text to Columns Wizard, Step 1, leave Delimited checked, and click Next.

iii. On the Footstep 2 screen, cheque Infinite and click Side by side.

4. For Pace 3, select Do not import column (skip) under Column data format. In the Destination, enter the prison cell where you lot want the extracted information to go (in this example $C$1) and click Finish.
This means yous will skip the first column (offset name), every bit yous don't demand information technology, and get only the second column extracted (last name) in Column C, starting from cell C1.

five. Now you accept last names in Column C next to the full names in Column B, and yous tin sort both columns by Cavalcade C.
To do that, select a column with last names and in the Ribbon, go to Dwelling > Sort & Filter > Sort A to Z.

6. In the pop-up warning, go out Expand the choice checked and click Sort. This means that you want all populated columns (B and C) to be sorted based on Column C.

After the sort is done, all values in Cavalcade B are sorted alphabetically by last name, and y'all tin can delete Cavalcade C, as you don't need it anymore.

Sort past Last Name With Wink Fill

The 2d choice to reach the same effect is to employ Wink Fill. This pick recognizes patterns by two or more entries.

1. Enter the outset two last names in cells C1 and C2 (Travis and McGoldrick) and select them. Now position the cursor in the lesser right corner of the pick area, until it turns into a cantankerous and double-click it.

2. Click on the Motorcar Fill options button that appears at the terminate of the selection and choose Flash Fill.

As you lot can meet, Excel automatically recognized that you desire to extract terminal names based on the infinite in Column B and populated the rest of Cavalcade C automatically. At present you can sort the range the aforementioned way as in the section above.

Sort by Last Name With Notice and Replace

Another way to sort by terminal proper name is to use Find and Replace.

1. Select Column B and copy it (CTRL + C), then select cell C1 and paste the copied column (CTRL + V).

2. Select Cavalcade C, and in the Ribbon go to Habitation > Find & Select > Replace (or employ the keyboard shortcut, CTRL + H).

3. In the pop-up window enter an asterisk and a infinite (* ) in Find what, leave Replace with blank, and click Supplant All.
In Find, an asterisk represents whatever graphic symbol, and so when you lot put asterisk-space in Notice, Excel will find all characters before a infinite and supervene upon them with an empty value (delete them).

Every bit a result, all first names and spaces are now removed from each cell. Only the last names are in Cavalcade C. Afterwards this, y'all can sort the column the aforementioned way equally in the section above.

Note: Another option is to use VBA code to sort data by concluding name.

Sort by Concluding Proper noun in Google Sheets

Since Google Sheets doesn't accept options like Excel'southward Flash Fill and Text to Columns, you lot can use functions to create a formula that will extract the concluding proper name in Column C. The same formula tin also be used in Excel with the same syntax.

one. Select prison cell C1, and enter the formula:

=Right ( B1 , LEN ( B1 )-FIND ( " " , B1 ) )

The LEN Function finds the length of text in jail cell B1 (which is 11).
The FIND Function returns the position of the text that you're searching for (space) in cell B1 (which is v).
The Correct Office returns the specified numbers of characters (xi–5 = half dozen) from the end, in the given cell (B1).
As the final event, you get the concluding six characters from B1, which is the last name (Travis).

2. Position the cursor in the bottom correct corner of cell C2 until the cross appears.

iii. Elevate the formula downwards to the terminate of the range (C8).

4. When you have all concluding names extracted, you can sort the information in Column B.
Select Columns B and C, and in the Card, go to Information > Sort range.

v. In the popular-upwardly window, select Column C and A → Z nether Sort past, and click Sort.

Now, you can delete Column C, equally the names in Column B are sorted past last proper name alphabetically,  from A to Z.

Source: https://www.automateexcel.com/how-to/sort-by-last-name/

Posted by: rosswharry.blogspot.com

Share this post