Author: Oscar Cronquist Article last updated on February 07, 2018

random-namesDebra Dalgleish at Contextures blog wrote an article Split First and Last Names in Excel. It gave me an idea about creating a formula that split the names and then also sort the names alphabetically.

To the right is a list with random names, separated by a comma.

To split the names and then sort them from A to Z. Select C2 to C13 and copy paste this formula: =LEFT(INDEX(A2:A13, MATCH(SMALL(IF($A$2:$A$13<>"", COUNTIF($A$2:$A$13, "<"&$A$2:$A$13)+1, ""),ROW()-1), COUNTIF($A$2:$A$13 ,"<"&$A$2:$A$13)+1,0)), FIND(",", INDEX(A2:A13, MATCH(SMALL(IF($A$2:$A$13<>"", COUNTIF($A$2:$A$13, "<"&$A$2:$A$13)+1, ""), ROW()-1), COUNTIF($A$2:$A$13, "<"&$A$2:$A$13)+1, 0)))-1) + Ctrl + Shift + Enter

Select D2 to D13 and copy paste this formula to extract forenames: =RIGHT(INDEX(A2:A13, MATCH(SMALL(IF($A$2:$A$13<>"", COUNTIF($A$2:$A$13, "<"&$A$2:$A$13)+1, ""), ROW()-1), COUNTIF($A$2:$A$13, "<"&$A$2:$A$13)+1, 0)), LEN(INDEX(A2:A13, MATCH(SMALL(IF($A$2:$A$13<>"", COUNTIF($A$2:$A$13, "<"&$A$2:$A$13)+1, ""), ROW()-1), COUNTIF($A$2:$A$13, "<"&$A$2:$A$13)+1, 0)))-FIND(",", INDEX(A2:A13, names-split-and-sortedMATCH(SMALL(IF($A$2:$A$13<>"", COUNTIF($A$2:$A$13, "<"&$A$2:$A$13)+1, ""), ROW()-1), COUNTIF($A$2:$A$13, "<"&$A$2:$A$13)+1, 0)))-1) + Ctrl + Shift + Enter

Download excel sample file for this tutorial.
(Excel 97-2003 Workbook *.xls)

Functions in this article:

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

Counts the number of cells within a range that meet the given condition

RIGHT(text;num_chars) returns the specified number of characters from the end of textstring