Extract Last Name from Full Name in Excel and M-Code

Today we are going to discuss about numerous methods in Excel and Power Query to extract last name from full name.

Excel Solution:

Method-1:

=LET(nm,A3:A7,TRIM(RIGHT(SUBSTITUTE(nm," ",REPT(" ",LEN(nm))),LEN(nm))))

Method-2:

=TEXTAFTER(" "&A3:A7," ",-1)

Method-3:

=MAP(A3:A7,LAMBDA(x,TAKE(TEXTSPLIT(" "&x," "),,-1)))

M-Code:

Method-4:

let

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

Result = Table.AddColumn(Source,"Last Name", each List.Last(Text.Split([Full Name] ?? ""," ")))

in

Result

Method-5:

let

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

Result = Table.CombineColumns(Source,{"Full Name"}, each List.Last(Text.Split(_{0} ?? ""," ")), "Last Name")

in

Result

Method-6:

let

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

Result = Table.AddColumn(Source,"Last name", (f) => Text.AfterDelimiter(f[Full Name]," ", Text.Length(Text.Select(f[Full Name]," ")) - 1))

in

Result