Separate first and last name in Excel using formula (3 Best Ways)

Excel, this software has accommodated many of its users to work efficiently and fulfill their tasks within time. Its spreadsheet let the user keep their data organized and well managed.

The software lets an individual manage what they want to separate or align. Now, separating the first and last names has a process, and the steps to do it are mentioned below.

Manage to separate full name by using Excel Text to Columns:

1. To split the names into two columns select the Full name row.

2. Go to Data tab > Texts to columns.

       3: Now, in convert text to column wizard choose delimited option and do next.

4. Choose a single or more delimiters and do next..

Here, first and last name separates through spaces between them. So, the section of data preview is displaying that our names have been placed rightly.

Note: if your name contains a middle name or is separated with a comma, then do check space and comma boxes in delimiters, and as a checkbox choose to treat consecutive delimiter ( mostly default selected).

5. Hence, choose the column data format then the destination, and click on finish.

Finally, the first and last name are divided.

Manage to separate first and last name by using Excel Formula:

Excel provides solutions in multiple manners, so the feature of the text to the column is very efficient but if you are looking forward to making a change in the actual names and want a smart way that automatically updates itself, you can divide formulas with the names.

Steps to separate first and last name in Excel

When your name is separated by one space character and it only contains your first and last name this formula is an ideal one to use.

First Name Acquiring Formula:

By using this generic formula the first name can be easily taken:

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

Note: you can acquire the FIND or SEARCH function to have space character position (“ “) from a cell, from where to exclude the space you are supposed to subtract 1. This number is shifted to the Left function for the extraction of number characters. Beginning from the string's left side.

Last Name Acquiring Formula:

For extracting the last name the generic formula is:

=RIGHT(A2,LEN(A2)-SEARCH( ,A2,1))

Separate First and Last name in latest Excel 2013, 16, 19 using Flash Fill:

Excel always tries to accommodate its user and provide them with the easiest and most smart solutions, and Flash fill is the easiest and most commonly used. Well, do you know that you can split your data through it?

The steps for it are:

1. Create a new column beside the one containing names, and type that part of the name which you want to extract from the first cell.

2. Again type the first name in the cell below. Now, Excel will sense a pattern (usually it does) and all you have to do is select your two cells and drag them or either do double click.

Note: The Flash Fill button is commonly modified through default. In case it is not enabling in excel of yours, then go to File> Options, go to Advanced and select the option box of Automatically Flash Fill.

So, did you understand the methods to separate first and last name in Excel Spreadsheet?

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.