How to split first and last name in excel using formula

WPS Excel has become a significant tool in the world of business. It permits its users to acquire various programming skills. It serves as a convenient platform in accessing analytical and computing features. Excel holds a prime importance in organizing and sorting data, managing finances and graphing. Excel plays a key role in compiling data which results in compacting of information at one place.

Although Excel is quite handy but it contains some technicalities. With proper skills and knowledge of different operations of Excel these technicalities can be handled easily . One of the main problem that is faced by a number of excel users is splitting of first and last name of full name. In this article we have penned down some easy and simple ways to split name. Following are some techniques that would prove to be fruitful in solving the mentioned problem.

How to spilt first and last name in excel using formula

Using formula for splitting full name is an appropriate and simple method. The following formula separates the full name in first and second name. The given steps will guide you to separate name by using formula

1. So the first step is to extract the first name. For this purpose we will use the formula =LEFT(A2,SEARCH( ,A2)-1)

2. Select the full name then enter the formula given above in second cell i.e. column B.

3. In column B we will get the first name.

1. Now for the second name we will use the formula =RIGHT(A2,LEN(A2)-SEARCH( ,A2,1))

2. Now we will again select the full name and will enter the formula in third cell i.e. column C.

3. In column C we will get the second name.

How to separate first and last name by comma

In splitting first and last name with comma we are going to imply the same steps mentioned previously. The only difference is a slight change in the formula that is the usage -2 instead of -1 as we have to remove both comma and space. For getting first name the formula will be =LEFT(A2,SEARCH( ,A2)-2)

 For extracting second name, we will use the formula=RIGHT(A2,LEN(A2)-SEARCH( ,A2,1))

How to separate middle name from full name

There are some names which consists of a middle name. To separate the middle name we will use a different formula that is =MID(A2,SEARCH( ,A2,1)+1,SEARCH( ,A2,SEARCH( ,A2,1)+1)-SEARCH( ,A2,1))

In this way you will be able to separate middle name from the full name.

How to split name by using flash fill

There is another technique of splitting name that is the usage of flash fill method.

1. In this method, first we will click data button then we will click flash fill which will activate the features of flash fill.

2. Then we will create a column in front of the column containing the original names.

3. After that we will type the part of the name that we want to extract.

4. Then we will type that part of name in the second cell. Automatically, the names will populate in all the other cells.

Did you get the concept of splitting full name in excel? To access more features of Word Document, PowerPoint slides and excel Spreadsheets, you can follow WPS Academy.

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