Have you ever thought of separating names in Excel? Finding the proper way to do so can be a bit troublesome. How do you separate names in Excel? Is there any way to separate and keep names in different columns without losing the original data? When you have a lot of data in Excel, it can be difficult to separate names from other fields. This can result in errors and slow down your work. The goal of separation in excel is to create names that contain exactly one word. This makes it easier to use as a title and easier for readers to understand what each name means.
This tutorial will help you easily split the names of people into columns in no time. You can also apply the tips given below to separate names in WPS Office too. Keep reading to find out more!
Part 1: Using Flash Fill to Separate Names in Excel
You don’t need to use any specific formula to separate names in first, middle, and last names in Excel. The easiest way you can separate the names is by using the “Flash Fill” feature. Using this feature, you can automate the process of separating names within seconds.
Here is how you can use the feature:
Step 1: Open your spreadsheet in the WPS Office.
Step 2: Against the column of the names you have already, create three different columns of First Name, Middle Name, and Last Name.
Step 3: Write the first name of the first person in the First Name column manually. Repeat the process for middle and last names in Middle Name and Last Name columns.
Step 4: Now, click on the first name in the First Name row, select all the cells below that name, and use the “Ctrl + E” shortcut.
Step 5: Select the Middle Name and Last Name rows one by one and repeat the process.
So in this case, the separate names in excel formula to use Flash Fill feature is the combination of “Ctrl + E.”
You will see three separate columns of names automatically sorted by First, Middle, and Last names. Once separated, you can easily copy the names and use them wherever you want.
The good thing about this feature is that it works perfectly for names that have a consistent first, middle, and last name combination. You can use this feature even if you have a large number of names in your sheet.
If you were looking for how to separate names in excel with a comma, you could use the method mentioned below.
Part 2: Using Text to Column to Separate Names in Excel
The Flash Fill is a great feature that can be used with little effort. However, the drawback of the Flash Fill feature is that it might be difficult to work with if you have several different sheets. You will have to enter names one by one with the Flash Fill feature.
The other way you can learn how to separate names in excel with space is by using the Text to Column feature. This is a stronger feature compared to Flash Fill, as it allows you to work with names that might have been separated by commas.
Step 1：Open your desired sheet in WPS Office Suite.
Step 2：Select the entire column that contains names.
Step 3：Click on the Data tab from the toolbar.
Step 4：Select the Text to Columns feature.
Step 5：Choose Text to Columns from the dropdown.
Step 6：Pick the Delimited feature from the dialog box and choose Next.
Step 7：Select the Tab and Space fields if names aren’t separated by commas, and click Next.
Step 8：Choose the General format and click on the Finish button.
These are the results you will get:
Using Smart Split Column Feature
The method mentioned above is a perfect choice for all situations that requires you to separate names. For example, with the method given above, you can separate names even if they are joined by double spaces or commas.
But the easiest way to separate names is by using the Smart Split column feature if all the names are present in singular cells. Here is how to use the function:
Step 1：Open your desired sheet in the WPS Office.
Step 2：Select the column from the sheet.
Step 3：Navigate to the “Data” tab and choose “Text to Columns.”
Step 4：Choose the option “Smart Split Columns.”
Step 5：Click on the “Finish” button to complete the process.
The names will be split in separate columns.
The good thing about this feature is that it allows you to save a lot of time compared to the “Text to Column” feature. Make sure you copy the data properly and review it after pasting in the sheet so the Smart Split Column feature can work properly.
Part 3: Using Formula to Separate Full Name to First, Last, and Middle Name
For example, you can learn how to separate names in excel into two columns if you are running a marketing campaign and have to contact your prospects properly. Here are some more methods to help you separate names from your data easily.
Separate Full Name Using Left Function
Step1：Open your desired spreadsheet in the WPS Office.
Step2：Click on any empty cell that doesn’t contain a word.
Step3：Enter the formula:
=LEFT(A3,5)
Step 4：You can change the formula to change the location of the cell.
The first letter after the bracket shows the name of the column. Digit right after the letter is the position of the cell.
The digit after the cell represents the number of characters we want to get from the given cell.
Step 5：You will now see the left string of name pasted in the new cell:
This simple method can help you separate the first name of any person with great ease without having to go through a tedious process.
Using Left, Right, and Mid functions to find all parts of a name
As mentioned above, you can use Left, Right, and Mid functions to find all parts of a name. By utilizing the Mid Function and Right Function respectively, you can find the Middle Name and Last Name of a person in no time.
Tips for Using Left Functions
Using these three functions can help you easily extract different parts of a person’s name. Compared to Smart Split or other features mentioned above, you will need to use this formula carefully.
First off, you will have to be specific about the position of the cell. For example, when using the LEFT function:
=LEFT(A3,5)
You should know that the digit after the first alphabet inside the bracket represents the position of the cell. In this case, the digit 3 refers to Cell 3, present in Column 3 of the sheet.
If you are going to use any other cell for separating the name, then you will have to use a different syntax of the formula. You will have to use to following formula if you are going to find the First Name of a person located in Cell 9:
=LEFT(A9,5)
Other than that, you also need to know about the digit that is present after the comma. This digit represents how many characters are going to be fetched from the cell you choose.
For example, the name “Joseph” has six characters. If you are choosing Cell 5 in Column A for extracting the name, then you will need to enter 5 and 6 in the formula, respectively, to get the full first name:
=LEFT(A5,6)
All these things mentioned above are applicable to all formulas whether you are using Left, Mid, or Right formula.
Separate Full Name Using Search Function
Using the Search Function is another great way you can split names in an Excel spreadsheet. The good thing about the Search function is that it is more versatile compared to the methods mentioned above. Here is how you can use the search function:
Find First Name
Step1：Open the file containing names in WPS Office.
Step2：Create separate columns for First, Middle, and Last names.
Notice that in our example, the column “A” contains the full names. The first name “William George John” is present on “A4” cell, and the last name “Henry Leslie Scott” is present on “A18” cell.
Step3：Use the following formula containing “SEARCH” function to find First Names:
=LEFT(Cell,SEARCH(" ", Cell)-1)
In our example, we have replaced the term “Cell” with “A4” to tell which cell we are referring to.
Step4：You will get the result by pressing “Enter:”
Now, you can click and drag the small square box appearing around the name “William” to get First Names from the whole range:
Find Middle Name
Step1：Start by using this formula:
=MID(Cell, SEARCH(" ", Cell) + 1, SEARCH(" ", Cell, SEARCH(" ", Cell)+1) - SEARCH(" ", Cell)-1)
As with the previous example, we will need to change the “Cell” with the name of the cell. We are going to put “A4” in the formulae to get Middle Names:
Step2：You will get the following result by pressing the “Enter” button:
Step3：You can now “drag” from the cell “D4” to get middle names from the entire range:
Find Last Name
Step1：Start by using this formula:
=RIGHT(Cell,LEN(A2) - SEARCH(" ", Cell, SEARCH(" ", Cell,1)+1))
In our example, we are going to change the Cell to “A4” as the names in our range start from A4.
Step2：You will now press the “Enter” button to get Last Name:
Step3：You can now “drag” from the name “John” to get last names from the range:
Difference between SEARCH function and “Left, Mid, Right” functions
Using the SEARCH function has certain advantages over simple “Left, Mid, Right” functions. For example, SEARCH function has the power of fetching names regardless of the number of characters. But using the Left, Mid, or Right function, you need to provide the number of characters in a name yourself.
FAQs about Separate Names in Excel
Q1: How to Combine First Name and Last Name in Excel?
The easiest way of combining names is by using the “&” function. Here is how to do it:
1. Open your desired sheet in the WPS Office.
2. Start by using the strings:
=A2&" "&B2
3. You can change the digits before A and B to see different combined names.
Q2: How to Separate Names in Excel into Rows?
There are several functions you can use to separate names into rows using Excel software. These methods are:
Flash Fill
Text to Column
Formulas
All these methods have been discussed above in this blog.
Q3. What is Flash Fill used for?
The Flash Fill feature is the fastest method for extracting the First, Middle, and Last names of people.
Q4. What is Text to Column used for?
The method of “Text to Column” is used for extracting text from given cells of a column. You can use the “Delimiters” to extract textual data separated by commas, spaces, tabs, semicolons, and so on. This method can help you find the first, middle, and last names of people easily.
Q5. How many formulas are there for separating names?
There are a total of three formulas:
Left formula
Mid formula
Right formula
The good thing about these formulas is that they can be used in different instances. You can customize these formulas the way you want to ensure that you can get desired results easily.
How to Separate Names in Excel
We hope that you learned how to separate names in excel formulas. If you need to deal with a lot of data regularly and have to separate the data as well, you should consider using WPS Office.
The good thing about WPS Office is that it allows you to perform functions on data effortlessly. You can use WPS Office Suite on all leading platforms. More than 500 million users worldwide trust WPS Office to create documents, spreadsheets, slides, and PDFs in no time.
