How to split, merge first name in Excel

Normally you enter the list of staff, students, students . The first name and last name fields are on the same column. However, in the process of working with some software, it is necessary to get the first name and last name separately, so the input data file separates the first and last column into two columns, the original file data of your first name field is 1 column. Don't rush to re-enter all data, TipsMake.com guides you in detail How to split and merge names in Excel helps you solve the problem quickly.

For example, there is a data file with original field name and first name in the same column:

How to split, merge first name in Excel Picture 1

When it is necessary to import data into Lac Viet system, the data file requires separate first name and last name into 2 columns Ten and HoLot:

How to split, merge first name in Excel Picture 2

Very simple you follow these steps:

Step 1: In the original data file, create 2 columns next to the First and Last column with the First name Middle and Last names -> Copy the entire contents of the First and Last column to the First Name column:

How to split, merge first name in Excel Picture 3

Step 2: Highlight the whole column Name content -> press Ctrl + H -> the dialog box appears:

- In the Find what section enter the * and space (write adjacent)

- Section Replace with : to plant without writing anything

Click Replace All to replace all content before the space with an empty string:

How to split, merge first name in Excel Picture 4

Step 3: A dialog box appears has been replaced -> click OK :

How to split, merge first name in Excel Picture 5

The result obtained First name from the First name column

How to split, merge first name in Excel Picture 6

Step 4: Enter the formula = LEFT (C4, LEN (C4) -LEN (E4)) to get the characters to the left of the First name column and the first name with the same number of characters as the string length in the first and last column minus the number of characters in the Name column :

How to split, merge first name in Excel Picture 7

Step 5: Press Enter -> copy the formula for the remaining values ​​to result:

How to split, merge first name in Excel Picture 8

Step 6: The remaining you copy and paste into the middle column and first name in the form of paste value to be able to copy and delete the first and last column:

How to split, merge first name in Excel Picture 9

Step 7: Now you can copy the contents of the first and last names column to the data file:

How to split, merge first name in Excel Picture 10

In addition, if you want to separate at the original data file -> delete the first and last name column

How to split, merge first name in Excel Picture 11

Results after removing the First and Last Name column:

How to split, merge first name in Excel Picture 12

If you do not copy and paste the Values value type in the 2 First and Last names column when copying or deleting the First and last name column with an error:

How to split, merge first name in Excel Picture 13

Include first and last name in Excel

In case, after separating 2 columns you want to combine the first name and first name column into one column, do as follows:

Create 1 more First and Last column next to the Last column -> enter the formula: = C4 & '' & D4 (the characters inside the quotation marks are spaces):

How to split, merge first name in Excel Picture 14

Pressing Enter copies the formula for the remaining values:

How to split, merge first name in Excel Picture 15

Perform Copy and paste the following types of values ​​to avoid errors when copying or deleting the relevant columns:

How to split, merge first name in Excel Picture 16

Now you can delete the 2 middle names and last names without any errors:

How to split, merge first name in Excel Picture 17

Above is a detailed guide on how to split and include names in Excel. Good luck!