How to Combine Two Columns in Excel: Easy Methods for Data Merging

Organizing data efficiently in Excel often involves combining information from different columns. Whether you need to merge names, consolidate codes, or join text for better data presentation, knowing How To Combine Two Columns In Excel is a valuable skill.

This guide provides straightforward methods to help anyone, regardless of their Excel proficiency, to merge columns effectively. Let’s explore these simple techniques to streamline your data management.

Method 1: Combining Columns Using the Ampersand (&) Operator

The ampersand (&) operator in Excel is a quick and direct way to concatenate text from different cells. This method is perfect for simple combinations and offers immediate results.

Steps:

1. Select the Target Cell: Choose the cell where you want the merged text to appear. For instance, if you are combining columns A and B and want the result in column C, select cell C2 to start.

2. Enter the Formula: Type the formula using the ampersand operator. The basic syntax is =Cell1&"Separator"&Cell2.

   • To combine cells A2 and B2 with a space in between, enter: =A2 & " " & B2.
   • To use a comma and a space as a separator, use: =A2 & ", " & B2.
   • Replace A2 and B2 with the actual cells you need to combine.

3. Press Enter: After typing the formula, press Enter. The cell will now display the combined content from the specified columns, separated as defined.

4. Apply to Other Rows: To apply this combination to the rest of your data, locate the fill handle (the small square at the bottom-right corner of the selected cell). Click and drag this handle down to copy the formula to all the rows where you need to combine columns.

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: Using the CONCAT Function to Merge Columns

The CONCAT function is another efficient way to combine columns in Excel. It offers a cleaner approach, especially when dealing with multiple columns or wanting to ensure readability of your formulas.

Steps:

1. Choose the Output Cell: Select the cell where you want the combined text to be displayed.
2. Input the CONCAT Formula: Enter the CONCAT formula in the selected cell. The syntax is =CONCAT(Cell1, "Separator", Cell2, ...).
   • To combine cells A2 and B2 with a space, type: =CONCAT(A2, " ", B2).
   • You can add more cells and separators as needed within the function.
3. Press Enter and Apply: Press Enter to execute the formula. Then, use the fill handle to drag the formula down and apply it to the remaining rows, just as you did with the ampersand method.

Tip: For older versions of Excel, you might find the function CONCATENATE instead of CONCAT. CONCATENATE works similarly but is typically replaced by CONCAT in newer versions for simplicity and efficiency.

Method 3: Combining Multiple Columns with TEXTJOIN

When you need to combine data from multiple columns and want to define a specific separator clearly, TEXTJOIN is the ideal function. This is particularly useful for creating lists or when you have many columns to merge with a consistent delimiter.

Scenario Example: Imagine you have customer addresses split across several columns (Street, City, State, Zip Code) and you want to create a single, comma-separated address string.

Steps:

1. Select the Destination Cell: Choose the cell where you want the full combined text to appear.

2. Use the TEXTJOIN Formula: Enter the TEXTJOIN formula. The syntax is =TEXTJOIN("delimiter", ignore_empty, Cell1, Cell2, Cell3, ...).

   • "delimiter": This is the character or string you want to use to separate each combined item (e.g., “, “, “-“, ” | “).
   • ignore_empty: Set to TRUE to ignore empty cells in your range, or FALSE to include the delimiter even for empty cells.
   • Cell1, Cell2, Cell3, ...: These are the cells or ranges you want to combine.

   For example, to combine addresses from cells A2 (Street), B2 (City), C2 (State), and D2 (Zip Code) with a comma and space separator, and ignore empty cells, the formula would be:

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

3. Apply the Formula: Press Enter to get the combined address in the cell. Drag the fill handle down to apply this formula to all relevant rows.

Note: The TEXTJOIN function is available in Excel 2019 and later versions, as well as in Microsoft 365. If you are using an older version, consider using CONCATENATE or the ampersand operator for similar results, or explore upgrading to a newer Excel version to take advantage of TEXTJOIN and other advanced features.

Method 4: Quick Column Merging with Flash Fill

Flash Fill is an intelligent Excel feature that automatically recognizes patterns in your data and applies the same pattern to other cells. This is an incredibly fast method for combining columns when you need quick, visually patterned results without formulas.

Steps:

1. Manually Create the First Combined Entry: In the column next to your data columns, manually type the combined result exactly as you want it to appear for the first row. 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”).
2. Initiate Flash Fill: Start typing the combined name for the second row in cell C3. As you begin typing, Excel will often recognize the pattern and show a preview of the Flash Fill results for the rest of the column.
3. Accept Flash Fill Suggestions: If the preview looks correct, press Enter to accept the Flash Fill. Excel will automatically fill in the rest of the column based on the pattern it detected.

Tip: If Flash Fill doesn’t start automatically, or if the preview isn’t correct, you can manually trigger it. Go to the Data tab on the Excel ribbon and click on Flash Fill in the Data Tools group.

Limitations to Consider with Flash Fill

While Flash Fill is very convenient, it’s important to be aware of its limitations:

• Static Results: Flash Fill results are not dynamic. If you change the original data in columns A or B, the combined data in column C (created by Flash Fill) will not automatically update. You would need to re-apply Flash Fill.
• Row Dependency: Flash Fill typically works best when the combined data is in the same rows, directly adjacent to the source columns.
• Pattern Sensitivity: If the data pattern is inconsistent or unclear, Flash Fill may not produce the desired results. It relies on recognizing clear, logical patterns.
• No Error Handling: Flash Fill processes data as it is. It doesn’t clean or correct data; therefore, ensure your source data is clean and consistent before using Flash Fill.

To deepen your understanding of Flash Fill and explore more of its capabilities, click here.

Method 5: Using Power Query for Advanced Column Merging

For users working with large datasets or needing more robust data manipulation during the merge process, Power Query is an excellent tool. Power Query offers powerful features for combining columns, along with data transformation and cleaning capabilities.

Here’s why Power Query is beneficial for combining columns:

• Handles Large Datasets Efficiently: Power Query is designed to handle large volumes of data without compromising performance, making it ideal for extensive Excel workbooks.
• Dynamic Data Updates: When your original data changes, Power Query queries can be refreshed to update the combined results automatically, ensuring your data is always current.
• Non-Destructive Operations: Power Query works by loading your data into a separate query environment, ensuring your original data remains unchanged and safe from accidental modifications.
• Advanced Customization: Power Query allows you to perform various data transformations while combining columns, such as filtering, cleaning, and formatting data—all within a single, streamlined process.

To understand why mastering Power Query is a crucial skill for modern Excel users, explore this resource.

Download Our Practice Workbook

Ready to practice combining columns in Excel and master these techniques? Download our free practice workbook designed to accompany this guide. This workbook includes:

• Practical Exercises: Hands-on tasks to help you practice combining columns using formulas like TEXTJOIN and CONCAT, as well as using Flash Fill and Power Query.
• Real-World Scenarios: Examples that mimic real data challenges, showing you how to effectively clean and merge data in project-like situations.
• Efficiency Tips: Discover shortcuts and best practices to make your column merging tasks faster and more efficient.

📥 Download the Workbook Now and start enhancing your Excel skills today! Whether you’re a beginner or an experienced Excel user, this practice file is designed to help you improve.

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

Leila Gharani

I’ve spent over 20 years helping businesses leverage data to achieve better outcomes. My background includes roles as an economist and consultant, along with 12 years in corporate management across finance, operations, and IT, overseeing SAP and Oracle projects.

As a 7-time Microsoft MVP, I possess extensive expertise in tools like Excel and Power BI. My passion lies in demystifying complex tech topics, empowering individuals to realize their capabilities. I am committed to continuous learning and dedicated to finding effective methods to support the success of others.

More About Leila | Join 400,000+ professionals in our courses