How to combine first and last name in excel


Intoduction :

We have already studied in detail about how to combine cells in excel. We also looked at some of the real life scenarios where we might be using these methods. One of such real life scenario is to combine first and last name in excel. Combining first name and last name is a scenario that we come across very frequently and can be used in any industry or institution. In this tutorial we'll discuss two methods to achieve our goal, so let's get going.

 

Method 1 : Combine first name and last name using the Ampersand operator ("&")

Here is the First method of combining first and last name in excel. In this method we'll make use of ampersand operator to join the values in two cells.

Step 1 :

Constructing the formula to combine first name and last name - Suppose the address of the cell  containing the first name is “F1” and the address of cell containing the last name is “G1”. Then our formula will be  :   =F1&” “&G1

How to combine first and last name in excel

 

Step 2 :

 

We’ll now enter the above formula in the cell in which we want to get the final combined name.

e.g. if we want to get the final combined name in the cell H1 then enter the formula ‘=F1&” “&G1’ in the cell H1.

 

How to combine first and last name in excel

 

Step 3 :

Suppose there is a whole column of first names and a whole column of last names and we want to create a new column which contains the full name i.e.  first name and last name combined then we just need to copy and paste the above formula in all the cells of the full name column.

 

Let’s try and understand this with an example.

Suppose column A has a list of first names, column B has a list of last names and column C should contain the list of full names.

 

How to combine first and last name in excel

 

Our formula will be =A1&” “&B1

 

How to combine first and last name in excel

 

 

Copy and paste this formula in all cells of column C.

 

How to combine first and last name in excel

 

 

Method 2 : Using the Concatenate function

Now we'll discuss the second method to combine first and last name i.e. by using the concatenate function.

 

Step 1 :

Constructing the formula to combine first name and last name using the CONCATENATE function - Suppose the address of the cell  containing the first name is “F1” and the address of cell containing the last name is “G1”.

Then our formula will be  :   =CONCATENATE(F1,” “,G1)

 

How to combine first and last name in excel

 

Step 2 :

We’ll now enter the above formula in the cell in which we want to get the final combined name.

e.g. if we want to get the final combined name in the cell H1 then enter the formula ‘=CONCATENATE(F1,” “,G1) in the cell H1.

 

How to combine first and last name in excel

 

Step 3 :

Suppose there is a whole column of first names and a whole column of last names and we want to create a new column which contains the full name i.e.  first name and last name combined then we just need to copy and paste the above formula in all the cells of the full name column.

 

Let’s try and understand this with an example.

Suppose column A has a list of first names, column B has a list of last names and column C should contain the list of full names.

 

How to combine first and last name in excel

 

Our formula will be = CONCATENATE(A1,” “,B1)

 

How to combine first and last name in excel

 

Copy and paste this formula in all cells of column C.

 

How to combine first and last name in excel

 

Conclusion :

This one was an important tutorial, frequent excel users will come across the problem of combining last name and first name very often. We learned two methods to achieve this in this tutorial - one by using the ampersand and the second is by using the concatenate function. The decision on which method  to use is totally ours, we can use whichever method we are comfortable in. Apart from combining names, another problem that we come across is combining line breaks i.e. combining the values of excel cells along with line breaks.