## Split first and last names and sort them alphabetically

*Article 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

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

Search for a text string in a data set and return matching records

Question: How do I find rows that contain a specific string value in a data set? Answer: Array formula in […]Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]### 3 Responses to “Split first and last names and sort them alphabetically”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

How does this formula affect adjourning cells with information related to each name ie phone number or address, etc.?

This formula does not sort any adjourning cells.

Is there a formula to split a list of items separated by commas into cells that match a heading?

for example:

cell A1: fin, pro, crm

cell A2: fin, crm, srm

cell A3: pro, crm

then split the list into a table with headings:

cell C1: fin

cell D1: pro

cell E1: crm

cell F1: srm

and notice how the 2 sets of cells don't have all the same data

thanks!!