Excel How To Combine Two Columns

Combining two columns in Excel is a common task used to merge names, addresses, product codes, dates, and other data into a single field. Consider this: whether you need to create a full name from first and last names, join a city with a state, or combine text and numbers, Excel offers several reliable ways to do it. The best method depends on your Excel version, the type of data you are working with, and whether you want a simple formula, an automated solution, or a one-time data cleanup. This guide explains Excel how to combine two columns using the most effective techniques, from basic formulas to Power Query and VBA.

Honestly, this part trips people up more than it should.

Introduction to Combining Two Columns in Excel

When people ask how to combine two columns in Excel, they usually want to place the contents of two cells into one cell. Take this: if column A contains “John” and column B contains “Smith,” the combined result should be “John Smith.”

Excel provides multiple methods for this task:

• Using the ampersand operator &
• Using the CONCAT function
• Using the TEXTJOIN function
• Using Flash Fill
• Using Power Query
• Using VBA/macros

Each method has advantages. Some are best for quick work, while others are better for large datasets or repeated tasks.

Method 1: Combine Two Columns with the Ampersand Operator

The simplest way to combine two columns is by using the ampersand symbol, known as the concatenation operator Simple, but easy to overlook..

Suppose you have this data:

If you want to combine column A and column B into one column, place this formula in cell C1:

=A1&" "&B1

This formula does three things:

1. Takes the value from cell A1
2. Adds a space using " "
3. Adds the value from cell B1

The result would be:

John Smith

You can drag the fill handle down to apply the formula to the rest of the rows No workaround needed..

Example with Text and Numbers

You can also combine text with numbers:

="Product ID: "&A2&" - "&B2

If A2 contains 105 and B2 contains Blue, the result will be:

Product ID: 105 - Blue

The ampersand method is fast, widely supported, and works in almost every version of Excel Which is the point..

Method 2: Combine Two Columns with the CONCAT Function

The CONCAT function is another easy way to combine text from different cells. It is similar to the older CONCATENATE function but more flexible and easier to use.

For two columns, you can use:

=CONCAT(A1," ",B1)

This combines A1, a space, and B1 into one result.

CONCAT with Multiple Cells

Unlike the ampersand operator, CONCAT can combine multiple ranges more easily:

=CONCAT(A1:C1)

This combines all values from A1 to C1 into one cell That's the part that actually makes a difference..

CONCAT with TEXT Functions

If one of the columns contains dates or numbers and you want to control the format, you can use CONCAT with TEXT:

=CONCAT(A1," ",TEXT(B1,"mmmm d, yyyy"))

If B1 contains a date, And that's what lets you display it in a readable format Easy to understand, harder to ignore..

Method 3: Combine Two Columns with TEXTJOIN

The TEXTJOIN function is useful when you want to combine text with a delimiter, such as a comma, space, hyphen, or slash Easy to understand, harder to ignore..

For example:

=TEXTJOIN(" ",TRUE,A1,B1)

This combines A1 and B1 with a space between them That's the part that actually makes a difference..

The formula uses three arguments:

=TEXTJOIN(delimiter, ignore_empty, text1, text2)

For example:

• delimiter is the separator you want, such as " " or ", "
• ignore_empty tells Excel whether to skip blank cells
• text1 and text2 are the cells you want to combine

Example

If A1 is blank and B1 contains “Smith,” this formula:

=TEXTJOIN(" ",TRUE,A1,B1)

will return only:

Smith

This is helpful when one column may be empty and you do not want extra spaces in the result.

Method 4: Combine Two Columns and Skip Blank Cells

Sometimes one of the columns is blank, and using the ampersand operator can create unwanted spaces. Here's one way to look at it: if A1 contains “John” and B1 is blank, this formula:

=A1&" "&B1

may return an extra space after “John.”

To avoid this, use TEXTJOIN:

=TEXTJOIN(" ",TRUE,A1,B1)

This skips blank cells and produces a cleaner result.

Example with Optional Middle Name

Suppose you have:

If you want to combine first, middle, and last names, use:

=TEXTJOIN(" ",TRUE,A1:C1)

This returns:

John Smith

and:

Maria Anne Garcia

TEXTJOIN is especially useful when combining multiple columns that may contain blanks.

Method 5: Combine Two Columns with Flash Fill

Flash Fill is a smart Excel feature that detects patterns in your data and fills the rest automatically.

For example:

To use Flash Fill:

1. Place the cursor in the first cell of the new combined column.
2. Manually type the expected result, such as John Smith.
3. Move to the next cell and type another example, such as Maria Garcia.
4. Press Ctrl + E.

Excel will recognize the pattern and fill the remaining rows.

Flash Fill is great for quick data cleanup when you do not need a formula. Still, if your source data changes later, the results will not automatically update.

Method 6: Combine Two Columns Using Power Query

For larger datasets, Power Query is one of the best tools for combining columns. It is especially useful when you need to repeat the process often or clean data before importing it into Excel.

To combine two columns using Power Query:

1. Select your data range.
2. Go to Data > From Table/Range.
3. In Power Query, select the columns you want to combine.
4. Go to Transform > Merge Columns.
5. Choose a separator, such