How to Combine Two Columns in Excel

Combining two columns in Excel can be done using different methods, such as the CONCATENATE function (or its newer variant TEXTJOIN in Excel 2016 and later), the & operator, or Power Query for more advanced operations. Below is a detailed table of how to combine two columns with step-by-step execution.

Table: Combining Two Columns in Excel

Method	Formula/Steps	Execution Details
Using & Operator	=A2 & B2	Combines columns A and B directly in each cell.
CONCATENATE Function	=CONCATENATE(A2, B2)	Older method to combine the values in A2 and B2.
TEXTJOIN Function	=TEXTJOIN(" ", TRUE, A2, B2)	Combines A2 and B2 with a space between. Ignores blank cells.
Power Query	Steps listed below	Used for combining columns in bulk with advanced options.

---

1. Using the & Operator

This is the simplest method to combine columns. You can use the & operator to join values from two cells.

Execution Steps:

1. Select a cell where you want the combined data to appear (e.g., C2).
2. Enter the formula:

   =A2 & B2

This will combine the values in columns A2 and B2.

3. Press Enter.
4. Use the fill handle (drag down from the corner of the cell) to apply the formula to other rows.

Example:

If cell A2 contains John and B2 contains Doe, =A2 & B2 will return JohnDoe.

Adding a Space or Separator:

• To add a space or separator between the combined values, use:

  =A2 & " " & B2

This will combine John and Doe as John Doe.

---

2. Using the CONCATENATE Function

The CONCATENATE function is an older method, still available in Excel, for combining columns.

Execution Steps:

1. Enter the formula:

   =CONCATENATE(A2, B2)

This will combine values from A2 and B2 without a separator.

3. Press Enter.
4. Use the fill handle to apply the formula to other rows.

Adding a Space or Separator:

• To add a space or separator, modify the formula:

  =CONCATENATE(A2, " ", B2)

---

3. Using the TEXTJOIN Function (Excel 2016 or later)

The TEXTJOIN function is more flexible, allowing you to combine text with a delimiter and ignore blank cells.

Execution Steps:

1. Select a cell where the combined data will appear (e.g., C2).
2. Enter the formula:

   =TEXTJOIN(" ", TRUE, A2, B2)

This combines A2 and B2 with a space as a separator.

3. Press Enter.

Adding Custom Separators:

• You can use any delimiter (e.g., comma, hyphen) by replacing the " " with the desired character:

  =TEXTJOIN("-", TRUE, A2, B2)

---

4. Using Power Query for Advanced Combination

If you need to combine columns for large datasets or advanced transformations, Power Query provides powerful tools.

Execution Steps:

1. Click From Table/Range to load your data into Power Query.
2. In Power Query:

• Select the columns you want to combine (e.g., Column A and Column B).
• Right-click and choose Merge Columns.
• Choose a delimiter (e.g., space, comma) or leave it empty.
• Enter a new column name for the merged result.

1. Click Close & Load to bring the data back into Excel.

Power Query is especially useful when combining columns across multiple rows or applying complex transformations to the data.

---

Comparison of Methods

Method	Pros	Cons
& Operator	Simple, easy for small datasets.	Can become complex with many columns.
CONCATENATE	Works in all versions of Excel.	Obsolete in newer versions of Excel.
TEXTJOIN	Flexible, ignores empty cells, supports delimiters.
Power Query	Advanced, great for large datasets, supports complex logic.	Requires more steps, suitable for advanced users.

These methods allow you to combine columns based on your data structure and specific needs, making Excel a flexible tool for data manipulation.