How to Combine Two Columns in Excel: 5 Simple Methods

Excel is a powerful tool for data organization, and sometimes you need to bring information from different columns together. Combining two columns in Excel, also known as merging or concatenating, is a common task for organizing names, addresses, or creating unique identifiers. This guide will show you five straightforward methods to combine columns, perfect for anyone looking to streamline their spreadsheets.

Let’s explore these easy-to-follow techniques to enhance your Excel skills.

Method 1: Using the Ampersand (&) Operator to Combine Columns

The ampersand (&) symbol is a quick and direct way to combine the text from two or more cells in Excel. It’s a simple formula that’s easy to remember and use.

Step-by-step guide:

Select the cell where you want the combined text to appear. For example, if you want to combine the data from column A and column B into column C, start by selecting the first cell in column C, like C2.
Enter the formula using the ampersand operator. The basic syntax is =Cell1&"separator"&Cell2.
- To combine cells A2 and B2 with a space in between, type: =A2&" "&B2
- To combine them with a comma and a space, use: =A2&", "&B2
- You can replace A2 and B2 with the actual cells you want to combine.
Press Enter. Excel will display the combined text in the cell you selected.
Apply to other rows (optional). To combine data in the rest of the rows, click on the fill handle (the small square at the bottom-right of the selected cell) and drag it down to apply the formula to the cells below.

Example: If cell A2 contains “John” and cell B2 contains “Doe”, using the formula =A2&" "&B2 in cell C2 will result in “John Doe”.

Method 2: Utilizing the CONCAT Function to Merge Columns

The CONCAT function is another efficient way to combine text strings in Excel. It’s especially useful when you are working with multiple columns or want a cleaner formula compared to using multiple ampersands.

How to use CONCAT:

Choose the cell where you want the combined result. For instance, if you are combining columns A and B, select cell C2.
Input the CONCAT formula. The structure is =CONCAT(Cell1, "separator", Cell2, ...).
- To combine A2 and B2 with a space, enter: =CONCAT(A2, " ", B2)
- You can add more cells and separators as needed within the function.
Press Enter. The combined text will appear in your chosen cell.
Extend to other cells (if necessary). Use the fill handle to drag the formula down to apply it to other rows in your spreadsheet.

Tip: For older versions of Excel, you might find the function name as CONCATENATE. Both CONCAT and CONCATENATE function similarly for combining columns in Excel.

Method 3: Combining Columns with TEXTJOIN for Delimited Lists

When you need to combine text from multiple cells into one, separated by a specific delimiter like a comma or a hyphen, TEXTJOIN is the ideal function. This is perfect for creating lists or combining data points into a single, readable string.

Using TEXTJOIN for column combination:

Scenario: Imagine you have a list of departments in column A (A2:A7), and you want to list them all in a single cell, separated by commas.

Steps:

Select an empty cell where you want the combined list to appear, for example, cell B2.
Enter the TEXTJOIN formula. The syntax is =TEXTJOIN("delimiter", ignore_empty, text1, [text2], ...).
- To join cells A2 to A7 with a comma and a space as a separator, use: =TEXTJOIN(", ", TRUE, A2:A7)
- ", " is the delimiter (comma followed by a space).
- TRUE tells Excel to ignore empty cells.
- A2:A7 is the range of cells you want to combine.
Press Enter. Cell B2 will now contain all the departments from A2 to A7, separated by commas and spaces.

Note: The TEXTJOIN function is available in Excel 2019 and later versions, including Microsoft 365. If you’re using an older version, consider using CONCATENATE or CONCAT in combination with delimiters, or explore upgrading to a newer Excel version to take advantage of TEXTJOIN.

Method 4: Fast Combining with Flash Fill

Flash Fill is an intelligent Excel feature that automatically recognizes patterns in your data and fills in values. It’s incredibly useful for quickly combining columns based on examples you provide.

Steps to use Flash Fill:

In the first row of an empty column, manually type the combined value exactly as you want it to appear, based on the data in the columns you want to combine. For example, if you’re combining first and last names from columns A and B, in cell C2, type the full name as it should appear (e.g., “Sheldon Cooper”).
Start typing in the cell below. As you begin typing in the next cell in column C (e.g., C3), Excel will often recognize the pattern and show a preview of the Flash Fill results in gray text.
Press Enter to accept the Flash Fill suggestions. If the preview looks correct, simply press Enter, and Excel will automatically fill in the rest of the column based on the pattern it detected.
Alternatively, use the Flash Fill command. If Flash Fill doesn’t start automatically, or to manually trigger it, go to the Data tab in the Excel ribbon and click on Flash Fill.

Limitations of Flash Fill:

Static results: Flash Fill doesn’t create a formula. The combined data is static and won’t update if the original data changes.
Row dependency: Combined data must be in the same row, directly next to the source columns for Flash Fill to work effectively.
Pattern dependent: Flash Fill relies on recognizing clear patterns. If your data is inconsistent or lacks a clear pattern, Flash Fill may not work correctly or produce inaccurate results.
No error handling: Flash Fill processes data as it is. It’s important to ensure your source data is clean and consistent before using Flash Fill.

For a deeper dive into Flash Fill and its capabilities, you can explore more resources online.

Method 5: Powerful Combination with Power Query

For handling large datasets or when you need more control over data transformation during the combination process, Power Query is an excellent tool within Excel. Power Query offers robust features for combining columns, along with data cleaning and transformation capabilities.

Why use Power Query for combining columns?

Handles large data efficiently: Power Query is designed to handle large datasets without performance issues, making it suitable for extensive spreadsheets.
Dynamic updates: Power Query can be set up to refresh your combined data automatically when the source data changes, ensuring your results are always up-to-date.
Non-destructive operations: Power Query works without altering your original data. It creates a new query output, keeping your source data intact.
Advanced customization: Power Query allows you to apply filters, perform data cleaning, and format your combined data all within the query editor.

If you’re interested in mastering data manipulation in Excel, learning Power Query is highly recommended.

Download our free practice workbook to get hands-on experience with combining columns in Excel using these methods. This workbook includes exercises for each technique, allowing you to practice and solidify your understanding of how to combine two columns in Excel effectively.

📥 Download the Workbook Now to start practicing and improve your Excel skills today!

Combining columns in Excel is a fundamental skill that can significantly improve your data management efficiency. Whether you choose the simplicity of the ampersand, the versatility of CONCAT and TEXTJOIN, the smart automation of Flash Fill, or the power of Power Query, Excel provides the tools to handle your data combination needs effectively. Start practicing these methods to enhance your Excel proficiency and data handling capabilities.

Published on: November 29, 2024

Last modified: November 29, 2024

Category: Excel

Tagged as: combine cells in excel, combine columns in excel, how to merge two columns in excel