How to Combine First and Last Names in Excel

📅 Jun 23, 2026 📝 Sarah Miller

Manually combining contact names in large spreadsheets is a tedious, error-prone task that drains valuable administrative time. Often, when exporting reports from standard funding sources or legacy donor databases, contact names are split into separate columns. Utilizing a dynamic Excel formula grants professionals total control over their data presentation, ensuring polished outreach. Notably, organizations like the Red Cross rely on these data-cleaning techniques to streamline donor communications. However, a key stipulation is that source data must be free of trailing spaces to avoid spacing errors. Below, we will demonstrate the exact formulas to seamlessly merge your name columns.

How to Combine First and Last Names in Excel

In the world of data management, Excel remains an indispensable tool for organizing, cleaning, and analyzing information. One of the most common tasks that database administrators, marketers, HR professionals, and general office workers face is merging data from separate columns. Specifically, combining a First Name and a Last Name into a single Full Name column is a routine operation.

Whether you are preparing a mailing list, formatting a directory, or cleaning up a customer database, Excel offers several ways to achieve this. In this comprehensive guide, we will explore the most effective formulas and techniques to concatenate first and last names in Excel, ranging from traditional formulas to modern functions and non-formula alternatives.

Understanding the Core Concept: Joining Strings

In spreadsheet terminology, joining pieces of text together is called concatenation. When you concatenate a first name (e.g., "John") and a last name (e.g., "Doe"), you cannot simply add them together like numbers. If you do, you risk ending up with "JohnDoe" without a space. Therefore, any formula you use must account for the names themselves and the; a space) that separates them.

Let's assume your data is structured as follows:

  • Column A: First Name (starting at row 2, cell A2)
  • Column B: Last Name (starting at row 2, cell B2)
  • Column C: Full Name (where the result will go)

Method 1: The Ampersand (&) Operator (The Quickest and Easiest Way)

The ampersand symbol (&) is Excel's built-in operator for joining text. It is widely considered the easiest and most readable way to combine text strings because it does not require memorizing complex function names.

The Formula:

=A2 & " " & B2

How It Works:

This formula tells Excel to take the value in cell A2, append a space character (enclosed in double quotes " "), and then append the value in cell B2.

Step-by-Step Instructions:

  1. Click on cell C2 (the cell where you want the full name to appear).
  2. Type =A2 & " " & B2 and press Enter.
  3. Hover your mouse pointer over the bottom-right corner of cell C2 until it turns into a black plus sign (the Fill Handle).
  4. Double-click the Fill Handle, or click and drag it down to copy the formula to the rest of your rows.

Method 2: The CONCATENATE Function (The Classic Approach)

For decades, the CONCATENATE function was the standard way to merge text in Excel. While Microsoft has officially designated it as a "legacy" function (replaced by newer functions in Excel 2016 and later), it remains fully compatible with all versions of Excel.

The Formula:

=CONCATENATE(A2, " ", B2)

How It Works:

The function accepts multiple text arguments separated by commas. In this case, we pass three arguments:

  • A2: The first name.
  • " ": A literal space character to separate the names.
  • B2: The last name.

Like the ampersand method, you can drag this formula down your column to instantly apply it to hundreds of rows.

Method 3: The CONCAT Function (The Modern Successor)

Introduced in Excel 2016, the CONCAT function is designed to replace the old CONCATENATE. It is shorter to type and offers better capabilities, such as the ability to combine entire ranges of cells without having to list them individually (though for simple first and last names, the syntax remains virtually identical).

The Formula:

=CONCAT(A2, " ", B2)

Use this function if you are working on modern versions of Excel (Excel 2016, 2019, 2021, or Microsoft 365) and prefer using formal functions over operators.

Method 4: The TEXTJOIN Function (The Most Powerful Tool)

If you are using Excel 2019 or Microsoft 365, TEXTJOIN is the most robust and elegant function for combining text. Unlike CONCAT, TEXTJOIN allows you to specify a; and automatically ignore empty cells.

The Formula:

=TEXTJOIN(" ", TRUE, A2, B2)

Why TEXTJOIN is Superior:

  • ; The first argument (" ") specifies that a space should be inserted between each text item.
  • Ignore Empty Cells: The second argument (TRUE) tells Excel to skip empty cells. This is incredibly helpful if some of your rows are missing last names or middle initials, preventing awkward double spaces.
  • Efficiency: You only need to type the; even if you are combining five different columns (e.g., Title, First Name, Middle Name, Last Name, Suffix).

Handling Advanced Scenarios

Real-world data is rarely perfect. Often, names are inconsistently capitalized, contain extra spaces, or need to be formatted differently (e.g., Last Name, First Name). Below are solutions to these common formatting challenges.

1. Reversing the Name Order (Last Name, First Name)

In many administrative databases, it is preferred to list the last name first, separated by a comma and a space. You can easily adjust any of the formulas above to achieve this:

=B2 & ", " & A2

If using CONCATENATE:

=CONCATENATE(B2, ", ", A2)

2. Fixing Capitalization with the PROPER Function

If your data entry was inconsistent (e.g., "jOHn doe" or "mary smith"), you can wrap your concatenation formula inside the PROPER function. This automatically capitalizes the first letter of each word and lowers the rest.

=PROPER(A2 & " " & B2)

This nested formula ensures that "john doe" becomes "John Doe", clean and professional.

3. Eliminating Extra Spaces with the TRIM Function

Sometimes, imported data contains hidden trailing or leading spaces (e.g., "John " instead of "John"). Combining these can result in double or triple spaces in your final column. Wrap your individual cell references in the TRIM function to strip away unnecessary spacing:

=TRIM(A2) & " " & TRIM(B2)

4. Incorporating Middle Names or Initials

If you have three columns-First Name (A), Middle Name/Initial (B), and Last Name (C)-and want to combine them, you must account for cases where some people do not have a middle name. Using a basic ampersand formula like =A2 & " " & B2 & " " & C2 will leave an ugly double space for individuals without middle names.

This is where TEXTJOIN shines:

=TEXTJOIN(" ", TRUE, A2, B2, C2)

If cell B2 is empty, TEXTJOIN skips it entirely and places only one space between the first and last name.

The Non-Formula Alternative: Flash Fill

If you do not want to write formulas and prefer a static, one-time solution, Excel's Flash Fill feature is incredibly fast and intuitive. It uses artificial intelligence to detect patterns in your data and fill out the rest of your column automatically.

How to Use Flash Fill:

  1. In cell C2, manually type out the combined name exactly how you want it to look (e.g., John Doe).
  2. Press Enter to move to cell C3.
  3. Start typing the combined name for the second row (e.g., Jane Smith). Excel will likely display a light-gray preview of the filled-in names for the remaining rows.
  4. If you see the preview, simply press Enter to accept it.
  5. If the preview does not appear, select cell C2, go to the Data tab on the Ribbon, and click the Flash Fill button (or press the keyboard shortcut Ctrl + E).
Note: Flash Fill is static. If you change a first name in Column A later, the full name in Column C will not update automatically. Use formulas if you expect your source data to change.

Summary Table: Choosing the Right Tool

Method Formula / Action Best For Pros / Cons
Ampersand (&) =A2 & " " & B2 Quick, everyday tasks. Simple, works on all versions, but can get messy with many columns.
CONCATENATE =CONCATENATE(A2, " ", B2) Older versions of Excel. Familiar to legacy users, but superseded by newer functions.
CONCAT =CONCAT(A2, " ", B2) Modern Excel users. Slightly shorter to type than CONCATENATE.
TEXTJOIN =TEXTJOIN(" ", TRUE, A2, B2) Handling middle names or missing data. Very powerful, ignores empty cells, but only available in Excel 2019+.
Flash Fill Keyboard shortcut Ctrl + E Quick, static results without typing formulas. Extremely fast, but does not update dynamically when source data changes.

Conclusion

Combining first and last names in Excel is a fundamental skill that saves hours of manual work. For dynamic sheets that must update automatically, the Ampersand (&) operator and the TEXTJOIN function are your best options. If you require a quick, one-off cleanup, Flash Fill is the perfect companion. By pairing these concatenation techniques with utility functions like PROPER and TRIM, you can ensure your final dataset is perfectly formatted, accurate, and ready for professional use.

Disclaimer:
The documents and templates provided on this page are for informational and illustrative purposes only. They do not constitute professional, legal, or financial advice, and should not be relied upon as such. Because individual circumstances and regulatory requirements vary, these materials may not be suitable for your specific needs. We recommend consulting with a qualified professional before adapting or using any of these examples for official or commercial purposes.