How to split the full name to first and last name in Excel?

1. Select the column where you have the names and surnames typed together.

2. Go to the "Data" tab and select "text to column" and select okay.

3. It will ask you about the separator criteria, like "space" "tab" "comma" etc., you will have to select only "space", as the names and surnames must have been typed keeping space between them.

4. Select Okay.

5. Note, that if you have other columns beside the name column in the original sheet, then you have to insert an empty column before you start this procedure.

Hope that works.

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.

Well, not only the instance of First name and last name, but you can take the text from one or more cells and then expand it across more cells. You just need to use Text to columns feature in Excel. The steps involved are -

1. Select the cell containing the text you would wish to split into two or more columns. Please note that if you want to do this for an entire column, like in your case, select the entire column.

2. Please note that you can select any number of columns, but should choose only one column. Also make sure there are enough empty columns to the right. You can add new columns if there is some data that can get overwritten.

3. Click on Data ->Text to Columns.

4. This will open Convert text to columns wizard. Click on Delimited ->Next.

5. Check the box that reads Space. Uncheck all other boxes. You can choose to use Comma and space among the check box items as well. The wizard will show a preview of how your data will look like.

6. Click on Next.

7. The next screen will let you choose a format for your new columns. You can let Excel do it for you.

8. Click on the Destination box. This will collapse your wizard.

9. Select the cells where you want to paste your formatted content. In your case, you will need to select two adjacent columns.

10. Return to the wizard and click on Finish.

This should be the easiest method to get your work done. Since it is based on wizard, you will get the things done almost automatically. Though there are few formulas that can be created, they may tend to be cumbersome to execute.