How to Concatenate Two Columns In Excel ( 5 Simple 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. For this session, we are using Excel 2019, feel free to use yours.

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

Data - Concatenate Two Columns In Excel

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 complex dataset.

Practice Workbook

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

Concatenate Two Columns

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

Full Name Column - Concatenate Two Columns In Excel

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

1. Concatenate Using Ampersand

We can use the ampersand sign (&) to concatenate the columns. This sign joins the elements on either side of it.

Let’s write the formula using the ampersand.

=B4&" "&C4

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

Ampersand Formula - Concatenate Two Columns In Excel

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.

Ampersand formula result - Concatenate Two Columns In Excel

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 values.

AutoFill Ampersand formula - Concatenate Two Columns In Excel

2. Concatenate Using 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. To know about the function, visit this article: CONCATENATE.

Let’s write the formula using this function.

=CONCATENATE(B4," ",C4)

CONCATENATE Formula - Concatenate Two Columns In Excel

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.

CONCATENATE Formula result - Concatenate Two Columns In Excel

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

CONCATENATE AutoFill - Concatenate Two Columns In Excel

3. CONCAT Function to Concatenate Columns

Like everything Excel is evolving every day (every version). From version Excel 2019 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.

Let’s form the formula using this function

=CONCAT(B4," ",C4)

CONCATE Formula - Concatenate Two Columns In Excel

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.

CONCAT Formula result - Concatenate Two Columns In Excel

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

CONCAT AutoFill - Concatenate Two Columns In Excel

4. TEXTJOIN Function to Concatenate 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.

To know about the function, visit this TEXTJOIN article.

Now, let’s concatenate two columns using TEXTJOIN.

=TEXTJOIN(" ",1,B4,C4)

TEXTJOIN Formula - Concatenate Two Columns In Excel

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).

TEXTJOIN Formula result - Concatenate Two Columns In Excel

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

TEXTJOIN AutoFill - Concatenate Two Columns In Excel

5. Concatenate Using Flash Fill

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

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.

Flash Fill - Concatenate Two Columns In Excel

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

Flash Fill result - Concatenate Two Columns In Excel

Concatenate Values Having 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.

New Example data - Concatenate Two Columns In Excel

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

Let’s use the CONCAT function.

=CONCAT(B4," ",C4,CHAR(10),D4)

Formula with line break - Concatenate Two Columns In Excel

You can notice, the first portion is a familiar one, we have concatenated the first and last name having space as delimiter. Then, we have used CHAR(10) which denotes a line break.

So, the address is joined with a line break.

Write the formula for the rest of the values, or use AutoFill.   

Line Break in concatenated value - Concatenate Two Columns In Excel

Conclusion

That’s all for today. We have listed several methods to concatenates 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.

shakil

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo