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.

**Table of Contents**Expand

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

*columns to complete the*

**Last Name****.**

*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 theand*First Name*columns respectively.*Last Name*

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:**

`=B5&","&C5`

**For Hyphen:**

`=B5&"-"&C5`

#### 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)`

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.

`=CONCAT(B5," ",C5)`

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

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

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

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****:**

- Letâ€™s use the
**CONCAT**function with the**CHAR function**.

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

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