How to Concatenate Two Columns in Excel (2 Suitable Methods)

While using Excel, circumstances may demand joining elements from two columns. To assist you then, we are going to show you today how to concatenate two columns in Excel. For this session, we are using Excel 365, feel free to use yours.

First things first, let’s get to know about the dataset that is the base of our examples.

Here we have several random peoples’ first and last names with respective addresses. Using this data, we will concatenate two columns.

Note that this is a basic table to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.

How to Concatenate Two Columns in Excel: 2 Effective Methods

There are numerous ways to concatenate columns in Excel. Let’s explore them with examples.

For example, we will concatenate the First Name and Last Name columns to complete the Full Name.

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

We can combine names in Two Excel columns using Excel functions like CONCATENATE, CONCAT, TEXTJOIN, etc. We can also use the joining operator Ampersand (&) to perform the same operation.

i. Using Ampersand Operator

This sign joins the elements on either side of it.

📌 Steps:

• Let’s write the formula using the ampersand on Cell D5.

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

You can see, we have used a couple of ampersand signs and in between the ampersands there is a " ". This is because we want to use a space to separate two names.

So inserting the first name (cell reference) we have joined the space with it using an ampersand and then the second ampersand concatenates the last name.

We have found the concatenated value, where first and last names are separated by a space.

• Write the formula or use the Excel AutoFill feature for the rest of the cells.

If you want to combine two columns with a comma or hyphen instead of a space, we can use the following formulas.

For Comma:

For Hyphen:

ii. Using Excel CONCATENATE Function

Excel provides various functions to join the columns together. One of them is CONCATENATE. The CONCATENATE function joins values together and returns the result as text.

📌 Steps:

• Let’s write the formula using this function.

Here, you may think, we have inserted two values. But there are three in particular. Space in the form of  " "  is the second text (text2) for the function.

This formula will provide the concatenated value.

• For the rest of the values, write the formula manually or utilize the AutoFill feature.

iii. Use CONCAT Function to Concatenate Columns in Excel 2016 or Later Versions

Like everything Excel is evolving every day (every version). From version Excel 2016 onwards there is a new function CONCAT to concatenate values.

The CONCAT function concatenates values supplied as references or constants. To know about the function, visit the Microsoft Support site.

📌 Steps:

• Let’s form the formula using this function.

The formula is similar to the formula using CONCATENATE. The CONCAT and CONCATENATE functions work similarly, to be honest, Excel brings CONCAT to replace CONCATENATE.

• Write the formula or exercise Excel AutoFill for the rest of the values.

iv. Using TEXTJOIN Function to Concatenate Two Columns

Another function we can use for concatenating the content of the columns is TEXTJOIN. The TEXTJOIN function concatenates several values together with or without a delimiter.

📌 Steps:

• Now, let’s concatenate two columns using TEXTJOIN.

Here, we have set space (" ") as the delimiter value. For the time being, we ignored empty cells that’s why 1 is in the second parameter. Then the cell references from two columns we want to join.

We have found the two-column values are joined together (image below).

• For the rest of the values, write the formula manually or utilize the AutoFill feature.

Read More: How to Merge Two Columns in Excel

2. Concatenate Two Columns Using the Flash Fill Feature in Excel

Flash Fill is another handy tool that concatenates two columns together.

📌 Steps:

• First of all, we need to write a name manually.
• Then, selecting the cell click the Flash Fill option from the Data Tools section from the Data tab.

You will find the concatenated values for the rest of the values in that column.

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

So far we have seen several ways to concatenate columns. And the values were separated by space. If you want a line break between the values then you will find this section helpful.

For example, we will find the Contact Info concatenating the info from each person.

📌 Steps:

• Let’s use the CONCAT function with the CHAR function.

You can notice, that the first portion is a familiar one, we have concatenated the first and last names having space as a delimiter. Then, we have to use CHAR(10) which denotes a line break. So, the address is joined with a line break.

• We need to adjust the height of Row 5.
• Go to the row number bar. Place the cursor between Rows 5 and 6.
• Then, double-click the cursor.

• Write the formula for the rest of the values, or use AutoFill and adjust the height of rows.

Download Practice Workbook

You are welcome to download the practice workbook from the following link.

Conclusion

That’s all for today. We have listed several methods to concatenate two columns in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other methods that we have missed here. Have a look at our website ExcelDemy for further articles.

Merge Columns in Excel: Knowledge Hub

<< Go Back to Merge in Excel | Learn Excel