How to Separate Names in Excel

Performing operations on names like search, sort, filter, etc. is usually easier when full names are separated into separate names of columns. In fact, making separate names is such a frequently performed data processing task, we think Excel should probably introduce a dedicated function for it.

In the meanwhile, however, there is a multitude of ways to separate names in Excel.

Using a Formula to Separate Names in Excel

The last method involves using formulae to separate names in Excel. There is a different formula to extract each part of the name. So, this method is probably the most complex one. That’s why we included it in the end. Oftentimes you might need to use extracted parts of the names for further processing. So, knowing the formula to extract each part of the name might come in handy in such cases.

Let us look at the formulae to extract the first, last, and middle names one by one:

Formula to Extract First Name

1. The formula to extract the first name from a given name string is as follows:

=LEFT(name_string,SEARCH(“ “,name_string)-1)

2. In the above formula, you just need to replace the name_string variable with a reference to the cell containing the name. So, in our example, if you want the first names to appear in column B, you can simply type the following formula in cell B2:

=LEFT(A2,SEARCH( ,A2)-1)

3. Then press the return key and copy the formula down to the rest of the cells in the column. Here’s the result you should get:

Formula to Separate Last Name

The formula to extract the last name from a given name string is as follows:

=RIGHT(name_string,LEN(name_string)-SEARCH(@,SUBSTITUTE(name_string, ,@,LEN(name_string)-LEN(SUBSTITUTE(name_string, ,)))))

1. So, in our example, if you want the last names to appear in column D, you can simply type the following formula in cell D2:

=RIGHT(A2,LEN(A2)-SEARCH(@,SUBSTITUTE(A2,,@,LEN(A2)-LEN(SUBSTITUTE(A2, ,)))))

2. Then press the return key and copy the formula down to the rest of the cells in the column. Here’s the result you should get:

Formula to Extract Middle Name

The formula to extract the middle name from a given name string is as follows:

=MID(name_string,SEARCH( , name_string)+1,SEARCH( ,SUBSTITUTE(name_string, ,@,1))-SEARCH( , name_string))

1.So, in our example, if you want the middle names to appear in column C, you can simply type the following formula in cell C2:

=MID(A2,SEARCH( ,A2)+1,SEARCH( ,SUBSTITUTE(A2, ,@,1))-SEARCH( ,A2))

2. Then press the return key and copy the formula down to the rest of the cells in the column.

Here’s the result you should get:

Explanation of the Formula

1. The MID function can help extract characters from the middle of any string if you can provide it with the starting position and the number of characters that you want to extract from that position.

2. The SEARCH(“ “,A2)+1 easily gives the starting position for the middle name (one position after the first space).

3. However, finding out how many characters to extract can be a little tricky.

4. You can use the SUBSTITUTE function to replace the last space character with an ‘@’ symbol.

5. You can then find the difference between the positions of the first and last spaces to get the total number of characters in the middle name.

=SUBSTITUTE(A2, ,@,1))-SEARCH( ,A2)

6. This gives the number of characters you want to extract.

Did you learn how to separate names in excel? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets, and PowerPoint Slides.

You can also download WPS Office to edit the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience.