Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

Dataset to show how to 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 more complex dataset.


Download Practice Workbook

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


2 Effective Methods to Concatenate Two Columns in Excel

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. 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.
=B5&" "&C5
  • B4 and C4 are the cell references of the first row from the First Name and Last Name columns 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.

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

AutoFill Ampersand formula - Concatenate Two Columns In Excel

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

For Comma:

=B5&","&C5

For Hyphen:

=B5&"-"&C5

Read More: Combine Text in Excel (8 Suitable Ways)


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.
=CONCATENATE(B5," ",C5)

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.

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

CONCATENATE AutoFill - Concatenate Two Columns In Excel

Related Content: Combine Text from Two or More Cells into One Cell in Excel (5 Methods)


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.
=CONCAT(B5," ",C5)

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.

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

CONCAT AutoFill - Concatenate Two Columns In Excel

Related Content: Combine Multiple Columns into One Column in Excel


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.
=TEXTJOIN(" ",1,B5,C5)

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.

TEXTJOIN AutoFill


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.

Flash Fill

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

Flash Fill result

There is also a keyboard shortcut. Press Ctrl+E for Flash Fill.


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:

=CONCAT(B5," ",C5,CHAR(10),D5)

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

Line Break in concatenated value


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.


Further Readings

Shakil Ahmed

Shakil Ahmed

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