Here are some of the ways you can try to split the name in two different cells in excel.
Most common method that Excel allows you to do to split the text into multiple cells.
1. Select the column that has the full name.
2. Click on Data > Text to Columns.
3. This opens text to columns wizard.
4. Select delimited and click on next.
5. Select "space" from the dialog.
6. Click next.
7. Check the destination to point out the destination cells where you want the formatted content.
8. After expanding the destination region. Come back to the wizard and click finish.
This should convert your full name into multiple cells as assigned by you. This works in your case perfectly as the wizard does the job for you.
how to split the full name to First name separate and Last name separate? Is there any formula for it?
You can use the following formula to split the full name into two different cells. Formula may work if it has two word form. In case of multiple words in full name, you have to modify the data to suite the output.
For example. To extract the first name say in A1 Cell into B1 cell. You can use the following formula.
=LEFT(A1, SEARCH(" ", A1) – 1)
In case of extracting the last name to the C1 cell, you have to use the following formula.
=RIGHT(A1,LEN(A1) -SEARCH(" ",A1))
This should help in extracting the two word full names. However in case of the full names with 3 words or 4 words, you may have to find the specific formula for the same.
I tried the formula given in online but it gives me either =left or =right formulas with particular character to split either side but not according to list of First name and Last name.
For that to work, your data has to be uniform at the first place. If your data itself has mispositioned first name and last name position, then you are forced to work on adjusting the content with manual work. Also with middle name in the full name, you'd not get the content with the first name and last name formula. Do note that in this method you can split based on comma, space, tab and semicolon. And this method does not work for the full names that are 4 words apart e.g. Sun Wen deming xian. The names from east and south India or china being such wide may be harder to split. In that case you have to use other formulas.