Quick Links
Summary
To merge first and last names in Excel, type the first combined name how you’d like it to appear, then use Flash Fill to combine the rest of the names automatically. Alternatively, get finer control by combining your name cells using a formula or the Power Query tool.
Do you want to bring the first and last names that are inseparate cells into a single cell? Combining text is easy in Excel, and we’ll show you five different ways to combine names.
Method 1: Use Flash Fill
Flash Fill is an Excel featurethat automatically fills your cells by recognizing the fill pattern. You manually fill a cell with the kind of data you want, and the feature senses that data and fills all your selected cells accordingly. No formula is necessary with Flash Fill.
In the case of combining names, you manually merge the first and last names for a single record, and Flash Fill recognizes that pattern and merges the names for all your other records automatically.
To use start using Flash Fill, in your spreadsheet, select the first cell where you want to display the combined names. In this cell, type the first and last names together as you’d like them to appear.
Go down a row and start typing the first and last name for your second record. As soon as you begin typing, Flash Fill will sense the pattern and fill all your records in gray color. To confirm you want these cells filled, press Enter on your keyboard.
If a Flash Fill prompt doesn’t appear, use the Ctrl+E shortcut to force a Flash Fill.
And that’s it.Excel has automatically mergedthe first and last names for all your records.
Method 2: Use the & (Ampersand) Symbol
If you prefer to use anExcel formulato combine names, a simple method forbringing multiple cells' content into a single cellis to use the&(ampersand) operator. This operator combines the content of your specified cells, allowing you to merge all the cell data you want.
To use this method, in your spreadsheet, select the cell where you want to see the merged names.
In the selected cell, type the following formula and press Enter. Here, replaceB2with the cell containing the first name andC2with the cell that has the last name.
Note that we’ve added a space enclosed with double quotes so that there’s a space between the first and the last name in the resulting cell.
In your selected cell, you’ll see the first and last names combined.
Tocopy this formula for all your records, from the bottom-right corner of the cell where you’ve entered the formula, click and drag downwards covering all your records.
If you have a middle name or initial in a separate column and you’d like to merge that as well, add that cell as an argument to the formula. Here, we’ll combine the contents of the cellB2,C2, andD2(in that order), with each cell content separated by a space.
Alternatively, if you want to combine names so that the last name appears first followed by a comma, use a formula like this.
Related:How to Merge Two Columns in Microsoft Excel
Method 3: Use the CONCAT Function
Excel’sCONCATfunction works just like the&operator and lets you combine multiple cells' content into a single cell. Using a function instead of an operator can sometimes make your formula easier for others to read and understand.
To merge your first and last names withCONCAT, in your spreadsheet, select the cell where you’d like to see the combined names.
In the selected cell, enter the following function and press Enter. In this function, replaceB2with your cell that has the first name andC2with the cell that contains the last name. Note that we’ve separated the first and the last name with a space in this function.
Once you press Enter, in your selected cell, you’ll see both your names merged. You can copy the function for all your records by dragging downwards from the bottom-right corner of the cell where the function exists.
If you’d like to merge the middle name or initial as well, use the function as follows. Here,C2denotes the middle name cell, whileD2contains the last name.
If, instead, you want the last name to come first, try changing around the formula and inserting a comma like this:
And that’s it.
Method 4: Use the TEXTJOIN Function
With theTEXTJOINfunction, you can combine your names using your specified delimiter. This function also offers the option to ignore empty cells. You’ll find it a little more complicated to use thanCONCAT, but it’s also a more powerful function.
To useTEXTJOIN, in your spreadsheet, click the cell where you want to display the merged names. Then, type the following function in the cell. Here, replaceB2with the cell that has the first name andC2with the cell containing the last name. To ignore empty cells, changeFALSEtoTRUE.
If your dataset has first name, middle name, and last name in theB2,C2, andD2cells, respectively, then you can join them using the following function:
Method 5: Use Power Query
Power Query is an Excel feature that helps youcombine data from multipleworksheets and workbooks. you may use this option to merge your columns as well.
To use this method, first, click a cell in your dataset. Then, from Excel’s ribbon at the top, select Data > From Table/Range.
In the “Create Table” box, ensure the correct dataset range is specified. If your dataset has column headers, enable the “My Table Has Headers” option. Then, select “OK.”
A “Power Query Editor” window will open. Here, hold down Ctrl (Windows) or Command (Mac) key and select your first name and last name columns. Then,right-clickeither column and choose “Merge Columns.”
On the “Merge Columns” window, click the “Separator” drop-down menu and choose “Space.” Optionally, in the “New Column Name” field, enter the name of the column that’ll have your merged names. Then, select “OK.”
In the “Power Query Editor” window’s top-left corner, click “Close & Load.”
In your workbook, Power Query has added a new worksheet that contains your merged names.
You now have a spreadsheet complete with readable full names for each entry. If you’re entering a lot of names and other data, learnthe best Excel functions for data entry.
