How to combine first and last name in excel

Answer

Combining first and last names in Excel can be done using formulas, Flash Fill, or Power Query

Using the Ampersand (&) Operator

The ampersand method is a quick way to join text strings:

1. Place the first name in A2 and last name in B2.

2. Enter the formula

   =A2 & " " & B2

3. Press Enter to see the combined result.

Tips:

• • Add middle names or titles by extending the formula:

                  =A2 & " " & B2 & " " & C2

Using the CONCATENATE Function

CONCATENATE joins multiple text strings into one cell:

             =CONCATENATE(A2," ",B2)

       =TEXTJOIN(", ",TRUE,A2:B101)

Handling Different Name Formats

• Last Name, First Name:

  =B2 & ", " & A2

• First Initial and Last Name:
  =LEFT(A2,1) & ". " & B2
• Full Name with Middle Initial:
  =A2 & " " & LEFT(B2,1) & ". " & C2

  
  

  
  

  Removing Extra Spaces

• Wrap formulas in TRIM to eliminate leading/trailing spaces:

• Power Query efficiently combines large datasets:

  1. Select your data range.

  2. Go to Data > From Table/Range.

  3. Select Add Column > Custom Column.

  4. Enter:

     =[First Name] & " " & [Last Name]

     5. Click OK to generate the combined column.

     Power Query preserves original data and updates dynamically when source data changes.

     Common Errors and Solutions

     • #NAME? Error: Check spelling of functions or Excel version compatibility.

     • #VALUE! Error: Convert numbers to text using TEXT function:

       =A2 & " " & TEXT(B2,"0")

     • Missing Spaces: Add " " between cell references.

     • Blank Cells Creating Extra Spaces: Use IF to handle empty cells:

       =IF(A2="","",IF(B2="",A2,A2 & " " & B2))