How to Concatenate Columns in Excel (8 Simple Methods)

Sometimes we need to concatenate the Excel columns together to make it more sense or to analyze data correctly. In this article, we’ll show you the effective ways to Concatenate Columns in Excel.

To illustrate, I’m going to use a sample dataset as an example. For instance, we have separated the Microsoft Services into two columns. In this case, we’ll show you how to Concatenate the two Columns in Excel with our proposed methods.

Concatenate Columns in Excel


Download Practice Workbook

To practice by yourself, download the following workbook.


8 Simple Methods to Unify Columns in Excel

1. Use Ampersand Symbol in Excel to Concatenate Columns

We know, many different symbols can be used in MS Excel and each of them has a unique operation. The Ampersand Symbol (&) is one of such kind. Firstly, we’ll use the Ampersand Symbol to Concatenate Columns in Excel. Therefore, follow the steps below to know how to use the symbol to link the columns.

STEPS:

  • At first, select cell D4. Here, type the formula:
=B4&C4

Use Ampersand Symbol in Excel to Concatenate Columns

  • Then, press Enter and use the AutoFill tool to fill the series.
  • As a result, it’ll return the combined column in column D.

Use Ampersand Symbol in Excel to Concatenate Columns

NOTE: Here, as we didn’t add spaces in the formula, the output is the merged columns without having any spaces between them.

But, if you want to have Spaces between the concatenated columns, follow the steps below.

STEPS:

  • Firstly, select cell D4 and type the formula:
=B4&" "&C4

Use Ampersand Symbol in Excel to Concatenate Columns

  • After that, press Enter. Then, use the AutoFill tool to fill the series.
  • Hence, the merged column with a space between them will appear in column D.

Read More: How to Concatenate Apostrophe in Excel (6 Easy Ways)


2. Apply Excel CONCAT Function to Combine Columns

Excel provides various Functions for carrying out multiple operations. In this second method, we’ll use the Excel CONCAT function to link columns. This function takes the cells and anything you input in its argument and combines them. So, follow the steps below to learn the method.

STEPS:

  • At first, select cell D4 and type the formula:
=CONCAT(B4," ",C4 )

Apply Excel CONCAT Function to Concatenate Columns

  • Then, press Enter and use the AutoFill tool to fill the series.
  • Finally, you’ll get the desired output.


3. Excel CONCATENATE Function for Combining

Additionally, we can also use the Excel CONCATENATE function for combining the columns.

STEPS:

  • Firstly, select cell D4. Here, type the formula:
=CONCATENATE(B4," ",C4 )

Excel CONCATENATE Function for Concatenating Columns

  • Then, press Enter. After that, use the AutoFill tool to fill the series.
  • Lastly, you’ll get the desired outcome.

Read More: How to Concatenate in Excel (3 Suitable Ways)


4. Unify Columns with ‘Merge & Center’ Feature

Another useful feature in Excel is ‘Merge & Center’ to combine columns. But, this feature returns only the upper-left value while ignoring other values. To use this feature, follow the steps are given below.

STEPS:

  • First, select the range of cells you want to work with.

Concatenate Columns with Excel ‘Merge & Center’ Feature

  • Then, select the ‘Merge & Center’ option from the Alignment group under the Home tab.

Concatenate Columns with Excel ‘Merge & Center’ Feature

  • Consequently, a dialogue box will pop out with a warning.
  • There, press OK.

Concatenate Columns with Excel ‘Merge & Center’ Feature

  • At last, it’ll return the merged column with the upper-left value only.

Read More: How to Merge Rows in Excel (2 Easy Methods)


Similar Readings:


5. Excel TEXTJOIN Function to Unite Columns

On the other hand, we can use the Excel TEXTJOIN function to unify the columns. To know how to use this function, follow the steps.

STEPS:

  • Firstly, select cell D4. Here, type the formula:
=TEXTJOIN(" ",TRUE,B4:C4)

Excel TEXTJOIN Function to Concatenate Columns

  • Then, press Enter and use the AutoFill tool like it’s shown in the following picture.
  • Finally, the concatenated column will appear in column D.

Read More: VBA to Concatenate Range with Separator in Excel (3 Ways)


6. Join Columns Using Excel Merge Columns Feature

Moreover, the Merge Columns feature in Excel Power Query Editor can link the columns together. Therefore, observe the steps given below to learn the process.

STEPS:

  • In the beginning, select the range of cells you want to work with.

Concatenate Columns Using Excel Merge Columns Feature

  • Then, select From Table/Range which is in the group Get & Transform Data under the Data tab.

Concatenate Columns Using Excel Merge Columns Feature

  • As a result, a dialogue box will pop out. And there, uncheck the note My table has headers as our selected range of cells don’t have a header.
  • After that, press OK.

Concatenate Columns Using Excel Merge Columns Feature

  • Subsequently, a new window will pop out and select the columns as shown in the image below.

Concatenate Columns Using Excel Merge Columns Feature

  • Now, select Merge Columns in From Text group under the Add Column tab.

Concatenate Columns Using Excel Merge Columns Feature

  • Consequently, a dialogue box will pop out again.
  • There, select Space from the Separator list and then, press OK.

Concatenate Columns Using Excel Merge Columns Feature

  • As a result, it’ll return a new column named Merged with the combined texts.

  • Afterward, close the Power Query Editor window and immediately, a dialogue box will pop out. There, press Keep.

  • Eventually, you’ll see a new Excel Sheet with the new Merged column.

Read More: Concatenate Multiple Cells but Ignore Blanks in Excel (5 Ways)


7. Excel CHAR Function for Combining Columns

Sometimes we need to concatenate the columns with a line break. Hence, in this method, we’ll use the CHAR function to combine columns in Excel with a line break. So, follow along to learn about the method.

STEPS:

  • At first, select Wrap Text, which you’ll get in the Alignment group under the Home tab.

Excel CHAR Function for Concatenating Columns

  • Then, select cell D4 and type the formula:
=TEXTJOIN(CHAR(10),TRUE,B4,C4)

Excel CHAR Function for Concatenating Columns

  • After that, press Enter and use the AutoFill tool to fill the series.
  • In the end, you’ll get your required concatenated column with a line break.

In Excel, the CHAR(10) formula represents a line break. Here, we used the TEXTJOIN function to combine the columns along with a line break between them.

Related Content: Macro to Concatenate Multiple Columns in Excel (with UDF and UserForm)


8. Use the ‘Flash Fill’ Feature in Excel to Unite Columns

Lastly, we’ll apply the Flash Fill feature for combining columns in Excel.

STEPS:

  • Firstly, select cell D4 and Type: Microsoft Outlook

Use ‘Flash Fill’ Feature in Excel to Concatenate Columns

  • Likewise, type the second one too.
  • Then, start typing the Third Cell and it’ll suggest the rest.

Use ‘Flash Fill’ Feature in Excel to Concatenate Columns

  • Finally, press Enter and it’ll return the remaining parts of the series like in the image below.

Related Content: How to Concatenate Two Columns in Excel with Hyphen (9 Quick Ways)


Conclusion

Henceforth, you will be able to Concatenate Columns in Excel with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo