4 Easy Ways to Combine Two Excel Columns: A Step-by-Step Guide

Microsoft Excel is a powerful tool for managing and analyzing data, but often, you’ll need to merge two columns into one for better organization, reporting, or analysis. Whether you’re dealing with first and last names, city and state, or product codes and descriptions, knowing how to combine columns efficiently can save time and improve data accuracy.

In this article, we’ll explore different methods to combine two Excel columns, from simple formulas to advanced automation techniques.

Why Combine Two Columns in Excel?

There are many practical reasons why you might want to merge two columns in Excel:

• Merging First and Last Names – Creating a full name column from separate first and last names.
• Combining Addresses – Merging street, city, and state into a single column.
• Unifying Data for Reports – Merging different columns for a clean data structure.
• Creating Unique Identifiers – Combining order numbers and customer IDs for better tracking.
• Cleaning Datasets – Making unstructured data more readable and useful.

4 Easy Ways to Combine Two Excel Columns

Excel offers multiple ways to merge data, depending on your needs and preferences. Here are four popular methods:

Method	Best For	Difficulty
Ampersand (&) Formula	Quick and simple text merging	Beginner
CONCATENATE / CONCAT Function	Merging text with flexibility	Beginner
TEXTJOIN Function	Combining multiple columns with delimiters	Intermediate
Power Query	Automating merging for large datasets	Advanced

Let’s dive into each method with step-by-step instructions.

Method 1: Using the Ampersand (&) Formula (Fastest Way)

The Ampersand (&) operator is the easiest way to merge two columns.

Example: Combine First Name and Last Name

First Name	Last Name	Full Name
John	Doe	John Doe
Jane	Smith	Jane Smith
Mark	Johnson	Mark Johnson

Steps:

1. Click on an empty cell where you want the combined text to appear.
2. Enter the formula: =A2 & " " & B2
   • A2 is the first name.
   • B2 is the last name.
   • " " adds a space between them.
3. Press Enter and drag the fill handle down to apply the formula to other rows.

Pros & Cons:

✅ Quick and easy to use.
✅ Works with any type of text.
❌ Not ideal for large datasets.

Method 2: Using CONCATENATE / CONCAT Function

The CONCATENATE function (older Excel versions) or CONCAT function (Excel 2016+) does the same thing as & but with better readability.

Example:

=CONCATENATE(A2, " ", B2)

or

=CONCAT(A2, " ", B2)

• Click on an empty cell where you want the combined data.
• Type the above formula: using CONCATENATE function for Excel 2016 and older / CONCAT function for Excel 2019 and later versions
• Press Enter and drag the fill handle down to apply it to other rows.

Adding a Space Between Values

To add a space between the values:

excelCopyEdit=CONCATENATE(A2, " ", B2)

Pros & Cons of CONCATENATE

✅ More readable than &.
✅ Works for numbers and text.
❌ Not available in older Excel versions (for CONCAT).

Method 3: Using TEXTJOIN for Advanced Merging

If you have multiple columns to merge, TEXTJOIN is a better option.

Example: Merging First, Middle, and Last Name

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

• " " is the separator (space).
• TRUE ignores empty cells.

First Name	Middle Name	Last Name	Full Name
John	A.	Doe	John A. Doe
Jane		Smith	Jane Smith
Mark	B.	Johnson	Mark B. Johnson

Pros & Cons:

✅ Best for combining multiple columns.
✅ Ignores empty cells.
❌ Only available in Excel 2016+.

Method 4: Using Power Query for Large Datasets

For large datasets, Power Query automates merging.

Steps:

1. Select Your Data: Click anywhere in the dataset.
2. Open Power Query: Go to Data > Get & Transform > From Table/Range.
3. Select Columns: Hold Ctrl and click both columns.
4. Merge Columns: Click Transform > Merge Columns.
5. Choose a Separator: Select Space or Comma.
6. Click OK > Close & Load.

Pros & Cons:

✅ Best for automating merging.
✅ Works with large datasets.
❌ Requires Power Query knowledge.

Handling Special Cases

1. Combining Columns with Numbers

If your columns contain numbers, Excel treats them as text when using &.

Example:

=A2 & "-" & B2

Useful for combining product IDs like 123-456.

2. Merging Columns Without Spaces

If you don’t need a separator, simply remove " " from the formula.

Example:

=A2 & B2

Will result in JohnDoe.

3. Removing Extra Spaces

If your data has unwanted spaces, use TRIM:

=TRIM(A2 & " " & B2)

Common Mistakes to Avoid

❌ Forgetting to Add a Separator

If you don’t add " ", words will merge without spaces.

❌ Merging Numbers Incorrectly

Always check if numbers are being converted into text.

❌ Using CONCATENATE in Newer Excel Versions

Use CONCAT instead of CONCATENATE in Excel 2016+.

Final Thoughts

Knowing how to combine two Excel columns efficiently can improve your workflow and data presentation. Whether you use &, CONCAT, TEXTJOIN, or Power Query, choosing the right method depends on your dataset and needs.

If you want to explore more about Formulas in Excel, check out this comprehensive guide for in-depth explanations and practical examples.