## Split first and last names and sort them alphabetically

*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

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

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

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 […]

I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]

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 […]

Table of contents Initials from first and last names Initials from first, middle and last names Create middle name initials […]

Find a sequence of values – wildcard search

In my last post I showed you how to find a sequence of values. The array formula extracted the row […]

The FIND function returns the position of a specific string in another string, reading left to right. Note, the FIND function […]

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

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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