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