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

Debra 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, 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

sort-by-two-columns
(Excel 97-2003 Workbook *.xls)

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])
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

COUNTIF(range,criteria)
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