There are times that you have data in separate columns in an excel spreadsheet but you would want to put those data into a single column. One of such instances is when you want to combine the first and last name of a person in a single column in excel.
The use case for this that I can think of right now is when you have a set of user registration data in a spreadsheet. In this spreadsheet, you are likely to have a separate column for the first name and another for the surname.
However, there are instances when you want to combine these two columns into a single column called full name. In such instances, you have to merge the two separate columns (first name and surname columns) into a single column (full name column).
This merging of columns of data into a single column with the respective data can be achieved in Microsoft Excel using a number of techniques.
Using the use case illustrated above, you are going to learn how to combine first and last names into a single full name column in excel in this article.
The various methods to combine first and last names to be explored in this article include:
- Excel Concatenate formula
- Ampersand (&) formula
- Text join function
- Flash fill
Let’s take a deeper look at each of the methods above using the sample data shown in the image below.
How to merge first and last names in excel using concatenate function
Using Microsoft Excel for work becomes very efficient if you know how to use the plenty of formulas that are at your disposal. One of such formulas that we are going to learn how to use now is the CONCATENATE formula. Follow the steps below to use the concatenate formula.
Note that in newer versions of Microsoft Excel, a new function called “Concat” has been introduced which plays the same role as the Concatenate function, hence you can use any of these interchangeably
A. Merging with a space separating first and last name
- Locate the two separate columns that you want to combine (first name and last name column)
- Pick the identity of the cell that contains the first name (e.g. Cell A2) and the last name (e.g. Cell B2)
- Click within the cell (e.g. Cell C2) where you want to display the merged name (under the Full Name column)
- Type an equal to sign (=) within the selected cell
- Follow the “=” with the function name (CONCATENATE or CONCAT)
- Introduce a bracket after the function name e.g. CONCAT()
- Within the bracket after the formula name, type in the following in the same order as mentioned below
- The identity of the first name cell followed by a comma (A2,)
- A double quotation marks separated by empty space after the comma (“ ”).
- A comma after the quotation mark followed by the identity of the last name cell (,B2)
- So in summary this is what you are supposed to type out =CONCATENATE(A2,” “, B2)
- Click on enter and you will find the full name (first name and last name separated by a comma) in the cell that you selected (Cell C2)
The logic behind the content in the bracket is this:
- The concatenate formula works by joining any elements put within the brackets. Each element in the bracket is separated from the next one with a comma. Hence, when you look at the formula used above, there are three different elements being separated by two commas.
- These three elements are A2, the empty space and B2. However, to make the Excel identify the empty space as an element, you have to enclose it with the double quotation marks (“ “).
- Once these three elements are put into the formula, it joins all the three separate elements into one like this (A2 B2) but since A2 and B2 are only identity of the cells, excel displays the exact value of the cells which is first name and last name respectively
B. Merging with a comma separating first and last name
If you would rather separate the first name and last name with a comma you would have to follow the steps illustrated above but then replace the empty space with a comma. In that case the second element in the CONCATENATE function becomes a comma surrounded by a double quotation sign (“,”).
Thus the new formula if you want the names to be separated by a comma becomes =CONCATENATE(A2,”,”, B2)
Also, if you want to leave some space after the comma before the last name then you have to introduce another element. In this case, you will have both the comma separated by double quotation marks and the empty space surrounded by double quotation marks.
This is going to give you a resultant formula as =CONCATENATE(A2,”,”,” “, B2)
How to combine first name, middle name and last name in excel using the CONCATENATE function
Using the same principles as illustrated above you can join multiple names including a middle name. So, if your full name consists of first name, middle name and last name then the formula becomes =CONCATENATE(A2,” “,B2,” “,C2).
In this scenario, A2 will be the first name, B2 will be the middle name and C2 will be the last name.
What if you want to use just the initials of the middle names?
Let’s take a look at how you can join the first, middle and last name but just print out the first name followed by the initial of the middle name then the last name.
How to combine first name, initial of middle name and last name in excel using the CONCATENATE function
To achieve this, we will use another function called LEFT. The LEFT Function allows us to print out a specified number of letters from the left. To use this function, write out the function with a bracket LEFT().
Provide two parameters, the first being the identity of the cell whose data you want to pick then the second being the number of characters you want printed out.
So, in the case of our middle name example, the first parameter will be the cell for the middle name (B2) then the second parameter will be one (1) since you want to print out just the first character of the middle name.
You can also introduce a full stop at the end of the initial as is usually recommended in official documents. This can be achieved with the formula below:
As such, to combine the first name, followed by the first character of the middle name then the last name, your formula will become:
=CONCATENATE(A2,” “,LEFT(B2,1),” “,C2)
If you want to introduce a dot or full stop right after the initial of the middle name before the last name then you would have to use the formula:
=CONCATENATE(A2,” “,LEFT(B2,1)&”.”,” “,C2)
One problem you are likely to encounter when using this method of concatenating first name, middle name (or initials) and last name is adding extra space or a dot in between the name of someone who does not have a middle name.
As such, you have to use the work around below to ensure that those who do not have middle names will not have an extra space or dot in between the first and last names.
You have to introduce an IF statement which is also another function in Microsoft Excel that checks if a specified condition is met or not and produces an output based on whether the condition was met or not.
So in this example, you will use the IF statement to check if the middle name exists before printing out the dot or print nothing if no middle name exists.
The if statement for this purpose will take 4 parameters.
|What is the condition
|What to do if the condition is met
|What to do if the condition is not met
In the above if statement, the formula is to check if the B2 cell is empty (equals nothing) and print YES. But the same formula will print NO if the cell B2 is not empty.
To apply this to the use case, you can check for the cell containing the middle name to see if it has the name or not. The output from this function will be used in the CONCATENATE function.
You can also use the initial of the middle name followed by a dot by including the LEFT function as illustrated below.
The if statement above will replace the second parameter in our CONCATENATE function.
=CONCATENATE(A4,” “,IF(B4=””,””,””&LEFT(B4,1)&”.”),” “,C4)
How to merge first and last names in excel using ampersand (&)
This works just like the concatenate function except that it is not a function but rather an operator. The ampersand (&) sign is used to join data in excel. This means by applying the ampersand between the first cell (A2) and the second cell (B2), the result will be the combination of their respective values in the order that you add them up.
To merge first and last names in excel using ampersand (&) follow the steps below:
- Select the cell where you want the merged name to appear
- Type in equal to (=) sign, followed by the cell identity for first name then that for last name
NB: This will merge the first name and last name without any space between them as shown below:
To add the space in between the first name and the last name, just enclose the empty space within double quotation marks and join it with the ampersand (&) as illustrated below:
It is also worth noting that you can apply all the other techniques that you did use with the CONCATENATE function in order to include the middle name or its initial followed by a dot.
CHALLENGE: Try and generate the full name from first name, middle name and last name but just print out the first letter of the middle name followed by a full stop.
Combine first and last name using the TEXTJOIN function
This is a new function that is available in Microsoft Excel 2019 version and Office 365. The textjoin function is actually the easiest way to merge data in two or more columns (cells) into a single column (cell).
If you don’t have the current version of Excel then you can use this function. However, the online version of Excel is always up to date hence you can use it.
How to use the TEXTJOIN function to merge the first name and last name
The TEXTJOIN function takes in three parameters.
- The first parameter is the separator to use (mostly either a space or a comma).
- The second parameter is a boolean value (TRUE/FALSE). This function checks if any of the cells is empty before executing. If the value is set to TRUE, it will ignore the empty cells and print out your output.
- The range that has the cells that you want to combine
I.e. =TEXTJOIN(“ ”,TRUE,A2:B2)
The formula above will combine the value or data from cell A2 to B2 and will ignore any empty cell. Because of the empty space as the first parameter of the formula, the full name will have. the first and last name being separated by a space.
With the TEXTJOIN function, you can join as many names or text as possible. All you need to do is to choose the right range for the cells. Let’s look at another example where I merge first name, middle and last name using the TEXTJOIN function.
Another advantage of this function is that because it checks for the presence of data in a cell before displaying, it doesn’t add an extra space when the middle name is empty as illustrated below:
CHALLENGE: Merge the first name, first letter of the middle name followed by a dot and last name into full name using the TEXTJOIN function
How to merge first and last name using flash fill
Flash fill is also another easy way to merge first name and last name into a single cell (full name). Flash fill is a feature in Microsoft Excel where the software tries to understand and identify certain patterns in order to apply the identified pattern to solve other problems.
For example, if you work out the solution for a particular problem, say you do the combination of the first and last name by yourself, Excel will then try to understand what you did then replicate that for subsequent ones.
To use flash fill, follow the steps below:
- Identify the cells (first and last name) you want to merge
- Identify the cell that you want to output the merge data in
- Type out the full name for the first set of cells
- Start typing out the full name for the second set of cells
- Automatically Excel detects the pattern and applies it to all the other cells
Sometimes, your Excel refuses to automatically invoke flash fill and you have to manually activate flash fill. There are two ways to do this.
The simplest one is to use the shortcut CTRL + E to invoke the flash fill feature. You just need to write out the first full name. Click on the cell for the second full name but this time round, press CTRL + E. This will automatically fill all the other cells with their respective full names.
Manually, you can locate flash fill using the steps below:
- Enter the full name for the first cell
- Click on the second cell and make sure that you are on the home tab
- Click on fill at the far right just below the AutoSum function to release the dropdown
- The last option on the dropdown is flash fill, click on that and it automatically fills the others
You may also be interested in “How to split one column into multiple columns in Excel“