2 ways to separate column Full and Name in Excel

When you enter data in Excel you have entered the full name and full name of everyone in the same box, but then you need to separate the middle name and last name into two different boxes to facilitate the management. How to separate Full and First column into 2 distinct column and first column names? This seems simple, but many people do not know how to do it. Please refer to how to do below of TipsMake.com.

1. Separate the column of first and last names in Excel

Step 1: Copy the entire column name and name (column B) to the new column (column D), this column will be the name column after separation.

2 ways to separate column Full and Name in Excel Picture 1

Step 2: Black out to select all the last and last names in column D.

Step 3: Press Ctrl + H , enter * (asterisk and spacebar) in the Find What box and then click Replace All .

2 ways to separate column Full and Name in Excel Picture 2

2 ways to separate column Full and Name in Excel Picture 3

Step 4: In cell C3 you enter the formula: = LEFT ( B3 , LEN ( B3 ) -LEN ( D3 )), you replace this cell B3 with the first cell containing the first and last names, D3 is the first cell containing the name just split.

2 ways to separate column Full and Name in Excel Picture 4

Inside:

1. Left is a function to cut a string from left, syntax = LEFT (cell containing the string to be cut from left, number of characters to cut)
2. Len is a function that takes the length of the string, syntax = LEN (cell containing the string needs to take length)

2 ways to separate column Full and Name in Excel Picture 5

Step 5: Drag the entire formula from cell C3 down to the rest of the cells.

2 ways to separate column Full and Name in Excel Picture 6

Note:

If you make a mistake in step 3, do not filter the name, check the original name column, make sure the input does not contain any characters (commas, periods, semicolons, . .), extra space in the name.

2. How to separate first and last names in Excel by combining multiple functions

We use the spreadsheet above.

Step 1: In cell D3, enter the following formula and press Enter:

= RIGHT (B3, LEN (B3) -FIND ("@", SUBSTITUTE (B3, "", "@", LEN (B3) -LEN (SUBSTITUTE (B3, "", "")))))

2 ways to separate column Full and Name in Excel Picture 7

If you are not familiar with the basic functions in Excel such as Right, Len, Find, Substitute, you just need to copy the above formula, replace your B3 cell with the last name and last name you need to paste it.

2 ways to separate column Full and Name in Excel Picture 8

Step 2: Drag the formula to the remaining cells in column D3.

2 ways to separate column Full and Name in Excel Picture 9

Step 3: This step is the same as step 4 of way 1, you enter cell C3 formula: = LEFT (B3, LEN (B3) -LEN (D3)) , then pull down all the cells.

2 ways to separate column Full and Name in Excel Picture 10

2 ways to separate column Full and Name in Excel Picture 11

When dragging the formula down, all the cells in column C3 will be successfully separated from the Full name column as follows:

2 ways to separate column Full and Name in Excel Picture 12

After separation, you need to delete column B, then blacken from C2 to end of D5 , press Ctrl + C then Ctrl + V , click on the paste option that appears after pasting, press V again or select the board icon number 123 to only paste the value of the Last and Last column name, now it is okay to delete column B. If you need to include the last name and last name, don't forget the previous instructions from TipsMake.com.

The video tutorial separates the name column in two ways above

3. Separate the first and last names in Excel with the Text to Columns command

Of course, having to remember commands for us is a nightmare. Especially the long, long-winded statements, not as simple as the first part above. Therefore, TipsMake.com will continue to guide you how to use automated commands to separate information of this name and name.

Black out the column of information to be separated, in this example is C3 - C12 , select Data > tab using the Text to Columns function as shown below:

2 ways to separate column Full and Name in Excel Picture 13

Step 1 appears, select the Delimited box then Next :

2 ways to separate column Full and Name in Excel Picture 14

Go to Step 2, you choose Space instead of the original Tab:

2 ways to separate column Full and Name in Excel Picture 15

Go to step 3, choose Text or because General . The Destination section is the cell that will contain the information after the split, here I select cell E3, ie the column They want to split. Then click Finish :

2 ways to separate column Full and Name in Excel Picture 16

However, when applying this formula, for long names (with 3 or more words), only the first and last names will be obtained. For Vietnamese-style names like Le Van A, the name (A) will be separated into another column. You should note and consider this issue:

2 ways to separate column Full and Name in Excel Picture 17

The above are 3 simple and common ways to extract information of Full Name in Excel without using other supporting software.

Good luck!