Suppose you have a large worksheet in which first and last name are combined together in a single column as a full name. Now, It’s difficult to split full name into first and last name in Excel. If you do this job manually then it will take lot of time. Now, there is no need to worry this article helps provide quick ways to split full name into first and last name in Excel. During preparing different reports we have to face different kind of problems. But if you have better knowledge then you can easily prepare proper reports in short time.
All we know very well Microsoft Excel is very popular electronic spreadsheet application software which is widely used around the world. You can easily solve your complex calculations, representing numerical data interm of charts, use different functions and many more to get the proper result.
When you generate or get any report from any software you will get full employee name in the report. Sometime you need to separate employee name in first and last name. But if you don’t know how to split full name into first and last name in Excel, you have to face problem. We understand about your problem and here we’ll discuss quick ways to easily split full name into first and last name in Excel.
Must Read: 2 Ways to Fill blank cells with 0 or any specific value in Excel
Steps to split full name into first and last name in Excel
You have full name in a single column and want to get the solution to separate them. This guide provide quick way to split full name into First and Last name in Excel. You have to use any given method which helps you to solve your problem.
Method 1: Using Convert text to column wizard
It is quick and simple method to split full name into first and last name in Excel. You have to take few simple steps which is given below to separate the full name into fist and last name.
Step 1: Select the range of cells A2:A8 in which you have full name.
Step 2: Click on the Data tab and choose “Text to columns” option. You can also press ALT+A+E shortcut key to directly open the convert text to column wizard.
Step 3: Select “Delimited” option and click on the “Next” button. Turn on the “Space” option and click on the “Next” button.
Step 4: Select “Text” option in column data format. Set the destination where you want to view the result and click on the “Finish” button to get the result.
Method 2: Using Left, Right, Len & Find Functions
If you have good command on the excel function than you have another way to split full name into first and last name in Excel. You can use Left, Right and Find excel functions to get the proper result. Before discuss this method we have to understand about this functions.
Left Function: Returns the specified number of characters from the start of any given text string in excel.
Syntax of LEFT Function: LEFT(text,[num_chars])
text: It is any text string which is containing the characters you want to extract.
num_chars: It specifies how many characters you want to extract from left side. It’s an option entry, if you omitted then excel by default extract first character from left side.
Right Function: Returns the specified number of characters from the end of any given text string in excel.
Must Read: MS Excel tips rapidly erase blank cells from excel
Syntax of Right Function: RIGHT(text,[num_chars])
text: It is any text string which is containing the characters you want to extract.
num_chars: It specifies how many characters you want to extract from right side. It’s an option entry, if you omitted then excel by default extract last character from right side.
Len Function: Len is text function which returns the number of characters on the given text string in excel.
Syntax of LEN Function: Len(text)
text: Fill the text whose length you want to count. Number of spaces count as a characters.
Find function: Find function is used to return the starting position of one text string with another text string. It works on case sensitive method.
Syntax of Find Function: Find(find_text, within_text,[start num])
find_text: It is the text which you want to find.
within_text: It is the text which containing that text which you want to find.
start_num: It’s an optional. It specifies the character at which to start the search.
Step 1: Open the existing worksheet in which you want to split full name into First and Last name in Excel.
Step 2: Type the following formula to get the First name in cell C2 =LEFT(A2,FIND(” “,A2,1)-1) and press enter to get the result. Now to get the result for rest of the cells drag the formula of C2 till C8 with the help of Fill handle button.
Step 3: Type the following formula to get the Last name in cell D2 =RIGHT(A2,LEN(A2)-FIND(” “,A2,1)). Press enter to get the result and drag the formula to rest of the cells up to D8. Now, full name are separated into different columns with the help of functions in excel.
Must Read: How to convert or change number to text format in Excel
I hope after reading this guide you can easily split full name into first and last name in Excel. If you have any suggestion regarding this article please write us in the comment box. Thanks to all.