With the first name in A2 and the last name in B2, the formula takes the following shape:ĭepending on the desired result, one of the following variations of the above formula may come in handy. Generally, you use the LEFT function to extract the first letter of the forename, and then concatenate it with the surname separated by a space character.
This example shows how to combine two names into one in Excel and convert a full name into a short name. This will prevent the appearance of extra spaces between words in rows where a middle name is missing:įormula 4. In situation when column B may or may not contain a middle name, you can handle each case individually, and then combine two formulas into one with the help of an IF statement: The screenshot below shows the first formula in action: Technically, you just add one more argument to the already familiar formulas to merge the middle name.Īssuming the first name is in A2, middle name in B2, and last name in C2, the following formulas will work a treat: In the generic form, here are the formulas to combine first and last name in Excel:ĬONCATENATE( first_name_cell," ", middle_name_cell," ", last_name_cell) Let's say, in your worksheet, you have one column for given name and another column for surname and now you want to join these two columns into one. Whenever you need to merge first and last names together in one cell, the fastest way is to concatenate two cells by using the ampersand operator (&) or the CONCATENATE function as shown in the below examples. How to join names in Excel by merging cellsĮxcel formula to combine first and last name.How to merge names in Excel automatically with Flash Fill.Merge last name and first name with comma.Combine first and last name separated by space.How to combine names in Excel with formulas.Below you will find some quick tricks to join names in Excel that will save you a lot of boring time. Luckily, the days of merging anything manually are over. In most situations, first and last names are stored in two separate columns, but occasionally you may need to combine two names in one cell. Remember, the column should be wide enough to write or merge all the texts in one cell.This tutorial is going to show you a few different ways to combine names in Excel: formulas, Flash Fill and the Merge Cells tool.Įxcel worksheets are often used for storing data about different groups of people - customers, students, employees, and so on. Else, it will not give you the desired result. However, before using the option, you’ll need to first increase the width of the column. Inside the Home tab (in your Excel worksheet) look for the Fill option. If you are not comfortable with VBA programming or macros, then you can use Excel’s Justify feature. Merge Cells using Excel’s Justify Feature I’ll loop through the Selection object to extract each cells value and concatenate the texts and numbers (if any) and store it in a variable.įinally, I’ll use the Selection object again, to assign the extracted values to a cell (Cells(1) or the first cell in the first row), align it according to my requirement and merge it. The Selection object represents the selected area in the worksheet. In the macro, I am using the Selection object as a Range. First, select or highlight the rows that you wish to merge and then hit the button. Now here's how you will merge all the cells value.