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

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

Functions in this article:

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

Related posts:

1. Sort values in parallel (array formula)
2. Sort numeric values by closest to farthest to a specific number in excel
3. Sort text values by length using array formula in excel
4. Extract a unique distinct list sorted alphabetically removing blanks from a range in excel