Combine First and Last Names in Excel: 4 Easy Ways

Combining first and last names in Microsoft Excel is a common task when you need a single full-name column for mail merges, reports, or sorting. This guide shows you four reliable methods—using the ampersand (&), the CONCATENATE function, the TEXTJOIN function, and Flash Fill—so you can pick the one that fits your data and skill level. No prior Excel expertise is needed, just a spreadsheet with separate first and last name columns.

What You’ll Need

• Microsoft Excel (2016, 2019, 2021, or Microsoft 365 for all methods; Flash Fill is available in 2013 and later)
• A spreadsheet with at least two columns: one for first names and one for last names
• An empty column where the combined names will appear (or the ability to insert one)

Step-by-Step

Below are four methods. The first is the most straightforward and works in every version of Excel.

Method 1: Using the Ampersand (&)

1. Open your Excel workbook and locate the columns with the first and last names (e.g., A and B).
2. Click on the first empty cell in a new column (e.g., C2).
3. Type the formula: =A2 & " " & B2 and press Enter. The ampersand concatenates text, and the space in quotes adds a space between names.
4. Drag the fill handle (the small square at the bottom-right of the cell) down to copy the formula to the rest of the rows.
5. Your combined names appear in column C. You can copy them and paste as values if you want to remove the formulas.

Method 2: Using CONCATENATE (Legacy)

1. Select the first empty cell in your target column (e.g., C2).
2. Type: =CONCATENATE(A2, " ", B2) and press Enter.
3. Drag the fill handle down to apply to other rows.
4. Note: CONCATENATE is still supported but has been replaced by CONCAT and TEXTJOIN in newer Excel versions. It works exactly the same as the ampersand method.

Method 3: Using TEXTJOIN (Excel 2019 / Microsoft 365)

1. In the first empty cell (e.g., C2), type: =TEXTJOIN(" ", TRUE, A2:B2) and press Enter.
2. The first argument is the delimiter (a space). The second, TRUE, ignores empty cells. The third is the range of cells to join.
3. Drag down to fill the column. TEXTJOIN is more flexible if you later have middle names or suffixes—you can expand the range.

Method 4: Using Flash Fill (Quick and Formula-Free)

1. In the first cell of your target column (e.g., C2), manually type the full name as you want it to appear (e.g., “John Smith”).
2. Press Enter, then start typing the next full name in C3. Excel will often detect the pattern and show a preview of the rest of the column in light gray.
3. Press Enter to accept the Flash Fill suggestion. If it doesn’t appear automatically, go to the Data tab and click Flash Fill (or press Ctrl+E).
4. Flash Fill is not a formula—it’s static text. If your source data changes, you’ll need to redo it.

Common Mistakes

• Forgetting the space: Using =A2&B2 gives “JohnSmith”. Always include the space in quotes.
• Using the wrong cell references: If you copy the formula down, Excel adjusts references automatically. But if you type a formula with absolute references (e.g., $A$2), it will repeat the same name for all rows.
• Overwriting original data: If you put the formula in the same column as the first name, you lose the first name. Use a separate column.
• Not converting formulas to values: If you delete the original columns, the combined names will break. Copy the results and paste as values (right-click → Paste Special → Values) if you want to keep them static.

Tips

• To handle middle initials or suffixes, adjust the formula: =A2 & " " & B2 & " " & C2 for three columns.
• To remove extra spaces from names (e.g., if some cells have leading/trailing spaces), wrap each cell with TRIM: =TRIM(A2) & " " & TRIM(B2).
• If you want a comma-separated format (Last, First), use: =B2 & ", " & A2.
• Use the CONCAT function (newer versions) instead of CONCATENATE: =CONCAT(A2, " ", B2)—it’s shorter and more modern.
• For large datasets, Flash Fill is the fastest, but formulas are more dynamic—choose based on whether your data changes.

Troubleshooting

• Formula shows as text: If the cell displays the formula instead of the result, the cell might be formatted as Text. Change the cell format to General, then re-enter the formula (click into the cell and press Enter).
• #NAME? error: This usually means you misspelled a function name (e.g., CONCATENATE vs CONCAT). Check the spelling and ensure you’re using a supported function for your Excel version.
• #VALUE! error: This occurs if one of the referenced cells contains an error or if the cell contains text that Excel cannot interpret in a formula context. Check the source cells for errors.
• Flash Fill not working: Make sure the data is in a consistent pattern. If Flash Fill doesn’t trigger, select the column and press Ctrl+E manually. Also, ensure there are no blank rows in the data.
• Combined names have extra spaces: Use TRIM as mentioned above, or use the CLEAN function to remove non-printable characters if the data was imported from another system.

Frequently Asked Questions

Can I combine first and last names without a formula?

Yes, use Flash Fill. Type the first full name manually, then press Ctrl+E to have Excel fill the rest based on the pattern.

How do I combine names with a comma (Last, First)?

Use the formula =B2 & ", " & A2 (assuming B is last name, A is first name).

What if some cells are blank?

If you use TEXTJOIN with TRUE as the second argument, blank cells are ignored. With ampersand, you’ll get extra spaces—wrap with TRIM or use an IF statement to handle blanks.

Will the combined names update if I change the original names?

Yes, if you use a formula (ampersand, CONCATENATE, TEXTJOIN), it updates automatically. Flash Fill does not update.

How do I remove the formula but keep the combined names?

Copy the column with the formulas, right-click on the same or another cell, choose Paste Special, and select Values. This replaces formulas with static text.

Can I combine more than two columns (e.g., title, first, middle, last)?

Yes, extend the formula to include all cells: =A2 & " " & B2 & " " & C2 & " " & D2 or use TEXTJOIN with a range.

Related

• Fix Command Prompt Not Working on Windows 11
• Block Edge Chromium Auto-Install in Windows 10
• Why Do Some Android Phones Come With Malware?