How to merge first and last name columns in excel

Answer

To merge first and last name columns in Excel, use one of these methods: a formula (ampersand, CONCAT, or TEXTJOIN), Flash Fill, Power Query / Get & Transform, or the CONCATENATE function on older Excel versions. Use formulas when you want a dynamic join that updates when source cells change. Use Flash Fill or Paste → Values when you want a one-time static column.

What is the simplest formula to merge the first and last name?

The simplest formula is the ampersand operator. Put this in the cell where you want the full name (for example, C2), then copy down. 

= A2 & " " & B2

What is the CONCAT function method?

Use CONCAT when you prefer a function name instead of an operator.

= CONCAT(A2, " ", B2)

What is the TEXTJOIN method and when should I use it?

Use TEXTJOIN when you might have empty values and you want to skip extra spaces. TEXTJOIN can ignore empty cells.

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

How to convert merged formulas to plain text (values)?

1. Select the column with formulas.

2. Press Ctrl+C (Windows) or Command+C (Mac).

3. On the Home tab, choose Paste → Paste Special → Values, or press Ctrl+Alt+V then V (Windows).

4. Press Enter.

How to use Flash Fill to merge names without formulas?

1. Type the correctly merged full name in the first row of the target column (for example, type John Smith in C2).

2. Select the next cell down in that column (C3).

3. Press Ctrl+E (Windows) or choose Data → Flash Fill (Mac: Data → Flash Fill). Excel will fill the rest following the pattern you gave.

4. Verify results and then convert to values if needed.

How to merge using Power Query / Get & Transform?

1. Select the table range and choose Data → From Table/Range (Windows) or Data → Get Data (Mac if available).

2. In Power Query Editor, select the FirstName and LastName columns (click first, hold Ctrl, click second).

3. Right-click one of the selected headers and choose Merge Columns.

4. Choose a separator (Space), give the new column a name, then click OK.

5. Click Close & Load to return the merged column to the worksheet.

Are there differences between Windows and Mac?

Yes.

1. Flash Fill shortcut: Ctrl+E works on Windows. Mac uses the Data → Flash Fill menu or Command+E in newer Excel for Mac versions that support the shortcut.

2. Power Query / Get & Transform: Windows Excel has fuller Get & Transform features. Mac has limited or newer support depending on Excel build and Office 365 subscription. Expect the Windows experience to be more feature-rich.

3. Paste Special dialog shortcuts differ: Windows often uses Ctrl+Alt+V, Mac uses menus (Edit → Paste Special or Home → Paste → Paste Special).

What if some rows have missing first or last names?

Use TEXTJOIN with the ignore-empty argument to avoid extra spaces. 

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

How to handle middle names or extra columns?

1. For First, Middle, Last use TEXTJOIN and list each column in order: 

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

2. For selective joining, use TRIM to remove accidental double spaces after concatenation: 

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

What if I need Last, First format (with a comma)?

Put the last name first, then a comma and a space, then the first name. 

= B2 & ", " & A2

Troubleshooting: common problems and fixes

1. Problem: Result shows #NAME?.
   Fix: Verify function name is supported by your Excel version (use & if CONCAT or TEXTJOIN are unavailable).

2. Problem: Extra spaces appear.
   Fix: Wrap the result with TRIM, for example = TRIM(A2 & " " & B2).

3. Problem: Power Query option missing on Mac.
   Fix: Update Excel to the latest build or use formulas/Flash Fill.

Quick checklist before finishing

1. Choose dynamic (formula) or static (Flash Fill → Paste Values).

2. Use TEXTJOIN when source cells might be blank.

3. Convert formulas to values to remove links to source cells.

4. Replace formulas with TRIM to remove extra spaces.

Example: Step-by-step using the ampersand, numbered

1. Click cell C2.

2. Enter the formula: 

   = A2 & " " & B2

3. Press Enter.

4. Drag the fill handle down or double-click it to copy the formula to the remaining rows.

5. To make the column static, copy the column and Paste Special → Values.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report