When working with Excel, you may encounter situations where combining two columns becomes necessary to improve data organization and facilitate your work. Whether you are creating full names, addresses, or performing data cleaning and analysis, merging columns can significantly enhance your ability to process and utilize data efficiently. In this tutorial, we will guide you through the process of combining two columns in Excel, whether you want to concatenate text, merge dates, or combine numerical data. Let's get started!

Combine two or multiple columns in Excel

• With ampersand (&)
• With a versatile tool
• With Excel concatenating functions

Combine columns containing formatted numbers (dates, currency, …)

• With TEXT function
• With Kutools’ Use Formatted Values option
• With Notepad

Optional: Convert formula-combined results to static values

Download combine examples (.xlsx)

Video: Combine two columns in Excel

Combine two or multiple columns in Excel

In this section, we will explore three methods to combine two or more columns. These techniques will enable you to merge and consolidate data in Excel efficiently. Whether you need to concatenate values, or consolidate information from multiple columns, these methods have got you covered. Let's dive in and discover how to merge columns effectively in Excel.

Combine two columns using ampersand (&)

Let's assume you have first names in column A and last names in column B, and you want to combine them to create a full name in another column. To achieve this, please perform the following steps.

Step 1: Input the formula with ampersand (&)

1. Select the top cell of the column where you want to combine the two columns, and input the below formula.

2. =A2&" "&B2

2. Press Enter.
3. 

Step 2: Copy the formula down to below cells to get all results

Double-click on the fill handle (the small green square in the lower-right corner) of the formula cell to apply the formula to below cells.

Merge columns quickly and easily using a versatile tool

Combining data using the ampersand symbol (&) from multiple columns can be a tedious process and prone to errors, as it requires typing &" "& and selecting multiple cells repeatedly. However, with Kutools for Excel, you can easily combine columns in just a few clicks, saving you time and effort.

Once you have selected the columns to be combined, select Kutools > Merge & Split > Combine Rows, Columns or Cells without Losing Data, and follow the steps below:

1. Select Combine columns.
2. Select a separator you need. In this example, I selected Other separator, and entered a comma (, ).
   Here we add a space after the comma to make the combined text easier to read.
3. Specify where you want to put the combined data.
4. Select how you want to deal with the combined cells.
5. Click Ok.
6. 

Result

In addition to the ability of combining columns and placing the result in a new location, the Combine Rows, Columns or Cells without Losing Data feature provides the added functionality of directly combining the source data in place.

Join two columns using Excel concatenating functions

Before we proceed with merging data from two columns into a single column using an Excel function, let's take a look at the following three commonly used functions for combining data. After that, we will dive into the step-by-step process.

Step 1: Select a blank cell where you want to put the combined data

Here I will select the cell C2, which is the top cell of the column where I will combine the two columns.

Step 2: Input the formula

Use one of the following formulas, and then press Enter to get the result. (In this example, I will enter the formula with the CONCATENATE function.)

=CONCATENATE(A2," ",B2)

=CONCAT(A2," ",B2)

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

Step 3: Copy the formula down to below cells to get all results

Double-click on the fill handle (the small green square in the lower-right corner) of the formula cell to apply the formula to below cells.

Combine columns containing formatted numbers (dates, currency, …)

Suppose you need to merge two columns, one of which contains formatted numbers. If you use a regular formula we talked above, the formatting of the numbers will be lost and you'll end up with the following results.

This is why it's important to format combined columns properly, especially when they contain a mix of text, numbers, dates, and other data types. To correctly display formatted numbers when concatenating, here are three methods you can follow.

• Combine with TEXT function
• Combine with Kutools’ Use Formatted Values option
• Combine with Notepad

Correctly display formatted numbers with TEXT function

In this section, I'll show you how to use the TEXT function to preserve the correct number formatting, and then combine the columns using the ampersand method as an example. Keep in mind that you can apply the same technique to the concatenating functions method as well.

Step 1: Select the formula that suits your data type

To combine Column 1 (text) and Column 2 (differently formatted numbers) in the example above while retaining their formatting, we can use the TEXT function to customize the display of the numbers. Below are the formulas that can be used to combine text with the formatted numbers mentioned above. You can simply copy the formula that suits your requirements.

Result

By utilizing the formulas mentioned above, you will be able to obtain the combined results with correctly displayed formatted numbers.

Correctly display formatted numbers with Kutools’ Use Formatted Values option

Kutools for Excel’s Combine Rows, Columns or Cells without Losing Data feature offers a Use Formatted Values option. By selecting this option, you can easily combine text and formatted numbers while correctly displaying their formatting without typing any formulas.

After selecting the columns that you wish to combine, proceed to select Kutools > Merge & Split > Combine Rows, Columns or Cells without Losing Data, and follow the steps below:

1. Select Combine columns.
2. Select a separator you need. In this example, I selected Space.
3. Specify where you want to put the combined data.
4. Select how you to want to deal with the combined cells.
5. Check the Use formatted values option.
6. Click Ok.
7. 

Result

The Combine Rows, Columns or Cells without Losing Data feature not only allows you to merge columns and store the result in a different location like other methods do, but it also enables you to directly merge the original data in place without losing any formatting.

Correctly display formatted numbers with Notepad

The Notepad method is an alternative approach to combine columns with formatted numbers, although it may involve a few additional steps. Once you become familiar with the process, it can be a fast and convenient method for combining data, especially when compared to using formulas.

Step 1: Copy the columns to be combined

Select the columns and press Ctrl + C to copy them.

Step 2: Paste the copied columns to Notepad

1. Press Windows key + S, type Notepad, then select Notepad from the results.
2. 
2. In the Notepad window, press Ctrl + V to paste the copied columns.
3. 

Step 3: Replace separator in Notepad with the one you need

1. Select the separator between the column values, then press Ctrl + C to copy it.
2. Press Ctrl + H to open the Find and Replace dialog box, and then press Ctrl + V to paste the copied separator to the Find what box.
3. Enter the separator you need in the Replace with box. In this example, I entered a space ( ).
4. Click Replace all.
5. 

Step 4: Copy the combined result to Excel sheet

1. To copy all the text in Notepad, press Ctrl + A to select everything, and then press Ctrl + C to copy the selected text.
2. Go back to your Excel worksheet, select the top cell of the desired location where you will put the combined result, and then press Ctrl + V to paste the copied text.
3. 

Result

Optional: Convert formula-combined results to static values

The combined column you created using the formula methods will be dynamic, which means that any changes in the original values will affect the values in the combined column. Additionally, if any of the source columns are deleted, the corresponding data in the combined column will also be removed. To prevent this, please do as follows.

Step 1: Convert formula to value

Select the values you combined using formulas, then press Ctrl + C. Next, right-click on any of the selected cells, and select the Values button from Paste Options.

Result

By doing this, you will paste only the values, removing the formulas. The combined values will become static and will not be affected by future changes in the original data.

Related articles

• Merge and Combine Columns without Losing Data in Excel
• If you merge multiple columns of data in Excel (no matter which Excel version you are using), only the left column of data will be kept, and the data of other columns will be deleted, if you apply the "Merge and Center" command from Home tab on the Alignment group. This tutorial is talking about how to merge or combine different columns of data into one cell or column in Excel.
• How to merge every other row/cell in Excel?
• If you have a list of data, and you want to combine every other row or cell of them as shown as below, do you know how to get it done? In Excel, you can use formula or VBA to merge every other row or cells.
• How to merge two tables by matching a column in Excel?
• Supposing you have two tables in two different sheets, one is main table, and the other is new data table. Now you want to merge these two tables by a matching column and update the data as below screenshot shown, how can you quickly solve it in Excel? In this article, I will introduce the tricks on combining two tables by a column quickly.
• How to merge multiple sheets with same headers in Excel?
• For example, you have some data with the same headers in different sheets as below screenshots shown, and now your job is to merge these sheets into one sheet. Instead of copying and pasting them one by one, this tutorial introduces the better ways for you to handle this job.