Excel: How to split names

Microsoft Excel has a plethora of uses in information processing. It can safely store data, including names, contact numbers, contact addresses, etc.

When working on your Excel, you may wish to split full names into first and last names. Sometimes, you may include the middle name. But how do we go about this? You can do this via many methods:


Split Names Using Text to Columns

By using this function, you will be able to split the values of the text in a column into separate cells.

Now, let's split the names in the image below into first and last names:


  • Check column (A2: A6) and select all the names.

  • Check the Data tab and click the tab with the label 'Data.'


  • Under the "Data" option, navigate to an option with the tag – "Text to columns." Click on it.


Now, navigate the cursor to the "convert text to column" Wizard and follow the straps below:

Step 1: Hover your cursor on "delimited", then you proceed to column wizard and click "NextSelect Delimited."

Step 2: Move the cursor to "space option' and click "NextSelect." "NextSelect" serves as the space option.


Step 3: Choose the destination cell. The existing cell can overwrite if you fail to choose the destination cell.

Click finish, and the names will be split into two columns.


Separate names using formula

You can also split names on your excel using formulas. This is a very quick and efficient method. Let's assume you have the following names.

1. Choose an empty column and name it "the first name." Type the formula =LEFT(A2,SEARCH(" ",A2)-1) in the cell you want to split it too. We choose B2 in this case

2. Choose another empty column and name it "The last name." Type the formula =RIGHT(A2,LEN(A2)-FIND(" ",A2,1)). The second or last name will be split into this cell, here it will be C2.

3. Click on the first name and drag to the remaining cells. Repeat the same to the next column

These two methods are great time savers. I hope this tutorial is helpful to you. Don't keep it to yourself; please share this post with your friends.