How to Concatenate Multiple Cells in Excel (7 Easy Ways)

In the sample dataset, we have three columns: ID, First Name, and Last Name for some people.

Method 1 – Using the CONCATENATE Function to Concatenate Multiple Cells

Steps:

• Select cell E5 in the dataset and enter the following formula:

Here, we have used the function formula to concatenate multiple cells with space.

• Press Enter to see the result, as the cell values from B5, C5, and D5 will be concatenated with a space through the formula.
• Use AutoFill to drag the formula to the lower cells of the column.

• Enter the following formula in cell E5:

• After pressing Enter, you will get the desired result.
• To get the desired result for the enter column, use Fill Handle.

Method 2 – Inserting Ampersand (&) Operator to Concatenate Multiple Cells

Steps:

• Enter the following formula in cell E5:

• Press Enter.
• Use AutoFill to fill the lower cells of the column.

Method 3 – Applying Merge & Center Command to Concatenate Multiple Cells 

From the following image, you can see that the cell values are not adjusted correctly. We will merge and center these values in the following steps.

Steps:

• Select cells B2:C2 to merge the first header of the data set.
• Go to the Home tab of the ribbon, and from the Alignment group, select Merge & Center.

• The cell value of B2 is merged and centered in the cell range B2:C2.

• Select cell range B4:C4 to merge and center the second column header in cell B4 of the dataset.
• Go to the Home tab and select Merge & Center.

• The cell value of cell B4 will be merged and centered after the previous step.

• Follow the previous steps for merging all the cell values one by one.
• Remember to merge the cell values one by one instead of choosing them all together at the same time. Otherwise, you will see only the first cell value after merging.

Read More: How to Merge Multiple Cells without Losing Data in Excel

Method 4 – Inserting a Line Break and ASCII Codes to Concatenate Cells

Steps:

• Select the cell range E5:E14.
• Go to the Home tab and select Wrap Text.
• After inserting CHAR(10), a line break will appear inside the output.

• Enter the following formula in cell B5:

• After pressing Enter, you will get the desired result with the line break before the full name.
• Drag the formula to the lower cells using AutoFill.

Method 5 – Using the TRANSPOSE Function to Concatenate Multiple Cells 

Steps:

• Enter the following formula in cell B14:

• After pressing Enter, you will see all the words in the same row but in different columns.

• To concatenate these words in one cell, use the following formula:

• Select the part inside the CONCATENATE function like the following image.
• Instead of pressing Enter, press F9.

• This process will convert all the cells into text functions at once.
• Inside the CONCATENATE function, you’ll see two types of brackets, remove the curly ones.

• Press Enter & you’ll find the whole range of cells (B4:B11) into a concatenated one.

Read More: How to Merge Cells Vertically Without Losing Data in Excel

Method 6 – Inserting the TEXTJOIN Function to Concatenate Multiple Cells

Steps:

• Select cell B14 and type the following formula:

• Here, ” ” means you’re adding spaces among all words, and TRUE denotes that the function will skip empty cells if found in your range of cells.

• Press Enter.

Method 7 – Applying the Fill Justify Command to Concatenate Multiple Cells 

Steps:

• Select cell range B5:B11.
• Increase the column width to adjust the output after merging.
• From the Home tab, select Merge & Center.

• The command will show you a warning regarding keeping the data only from the first cell after merging.

• Select the desired cell range first.
• From the Home tab, select the Fill dropdown in the Editing group.
• Select Justify from the dropdown.

The previous step will merge all the cells into one without losing any data.

Read More: How to Combine Cells with the Same Value in Excel

Download the Practice Workbook

You can download the free Excel workbook here and practice on your own.

Excel Concatenate Multiple Cells: Knowledge Hub

• How to Merge Cells Using Excel Formula
• Combine Multiple Cells into One Separated by a Comma in Excel
• Concatenate Multiple Cells Based on Criteria in Excel
• Concatenate Multiple Cells but Ignore Blanks in Excel
• How to Combine Cells into One with Line Break in Excel
• How to Combine Two Cells in Excel with a Dash

<< Go Back To Concatenate Excel | Learn Excel