Sort Full Name by Last Name in Excel

Today, we will talk about sorting Full Names in Excel by Last Names.

The input data is located in the range B3:B12.

We need to sort the data according to last names.

Method-1:

Formula in D4 cell,

=SORTBY(B4:B12,TEXTAFTER(B4:B12," "))

The TEXTAFTER function extracts text following a specified delimiter, which is " ".

The function's output is:

{"Girvin";"Mourad";"Gharani";"Bansal";"Rothstein";"Tracy";"Murray";"Rydobon";"Verma"}

The SORTBY function allows us to sort one array based on another array.

We will sort the range B4:B12 using the array provided by the TEXTAFTER function.

Method-2:

=LET(a,B4:B12,SORTBY(a,MID(a,FIND(" ",a)+1,25)))

This is similar to method-1, but instead of using TEXTAFTER, we utilize a combination of the MID and FIND functions to extract the sort_by array.

Method-3:

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

Result = Table.Sort(Source,{each Text.AfterDelimiter([Full Name]," ") })

in

Result

Method-4:

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

Result = Table.Sort(Source,{each Text.Middle([Full Name], Text.PositionOf([Full Name]," "),25)})

in

Result

Method-5:

I will demonstrate technique that is compatible with Excel 2016 and earlier versions.

We will need a helper column that will adjust according to the input data.

Helper Column Formula in G4 cell:

=MID(B4,FIND(" ",B4)+1,25)

Extend this formula down based on the input data.

In cell D4, use the following formula:

=INDEX($B$4:$B$12,INDEX(MATCH(ROW($B$4:$B$12)-ROW($B$4)+1,COUNTIF($G$4:$G$12,"<="&$G$4:$G$12),0),ROWS($D$4:D4)))

Instead of pressing Enter, use CTRL + SHIFT + ENTER and then drag the formula down the column corresponding to our expected results.