How to Concatenate Two Columns in Excel – 2 Methods

This is the sample dataset.

To concatenate the First Name and Last Name and get the Full Name.

Method 1. Combine Names in Two Columns with Space/Comma/Hyphen Using an Excel Formula

Combine names in two Excel columns using the CONCATENATE, CONCAT, TEXTJOIN Functions and the Ampersand.

i. Using the Ampersand Operator

Steps:

• Enter the formula using the ampersand in D5.

• B4 and C4 are the cell references of the first row from First Name and Last Name columns.

Between the ampersands there is a " ". to use a space to separate the two names.

The first name (cell reference)  was entered, the space was added using an ampersand and the second ampersand concatenated the last name.

• Drag down the Fill Handle to see the result in the rest of the cells.

To combine two columns with a comma or hyphen instead of a space, use the following formulas.

For Comma:

For Hyphen:

ii. Using the Excel CONCATENATE Function

Use the CONCATENATE function to join values and return the result as text.

Steps:

• Enter the formula.

Space:   " "  is the second text (text2) in the function.

• Drag down the Fill Handle to see the result in the rest of the cells.

iii. Use the CONCAT Function to Concatenate Columns

The CONCAT function concatenates values supplied as references or constants.

Steps:

• Enter the formula.

The CONCAT and CONCATENATE functions work similarly.

• Drag down the Fill Handle to see the result in the rest of the cells.

iv. Using the TEXTJOIN Function to Concatenate Two Columns

Use the TEXTJOIN function.

 Steps:

• Enter the formula.

The space (" ") is set as delimiter. Empty cells are ignored, using 1 in the second parameter.

• Drag down the Fill Handle to see the result in the rest of the cells.

Read More: How to Merge Two Columns in Excel

Method 2 – Concatenate Two Columns Using the Flash Fill Feature

Use the Flash Fill.

Steps:

• Enter a name manually.
• Select the cell.
• In the Data tab, select Data Tools.
• Click Flash Fill.

This is the output.

How to Concatenate Values of Two or More Columns in Excel with a Line Break

To add a line break between the values:

To find the Contact Info:

Steps:

• Use CONCAT function with the CHAR function.

CHAR(10) is used for a line break. The address is joined with a line break.

• Adjust the height of Row 5.
• Go to the row number bar. Place the cursor between Rows 5 and 6.
• Double-click.

• Drag down the Fill Handle to see the result in the rest of the cells.

Download Practice Workbook

Download the practice workbook.

Merge Columns in Excel: Knowledge Hub

<< Go Back to Merge in Excel | Learn Excel