## Split first and last names in excel and then sort them alphabetically

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

### Category: Excel

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.Comments(445) Filed in category: Excel, VLOOKUP and return multiple values

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]Comments(249) Filed in category: Concatenate, Excel, Textjoin

Comments(161) Filed in category: Charts, Excel, Interactive

### 3 Responses to “Split first and last names in excel and then sort them alphabetically”

### Leave a Reply

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

<code>your formula</code>

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