How to Combine Columns in Excel Using Formulas or Flash Fill

Combining data from multiple columns into one is a fundamental task in Excel, whether you are cleaning up a mailing list, merging first and last names, or creating unique identifiers for a database. While the task seems simple, choosing the wrong method can lead to messy data or broken formulas when you delete the original columns.

The quickest way to combine columns in Excel is by using the Ampersand (&) operator. For example, if your data is in cells A2 and B2, you would enter =A2 & " " & B2 into a third cell to join them with a space in between.

This article explores five professional methods to merge columns, ranging from simple shortcuts to powerful functions that handle complex data formatting.

How to Use the Ampersand (&) Operator for Fast Merging

The Ampersand symbol is the most versatile tool for concatenating (joining) text. It is technically an operator, not a function, which makes it incredibly fast to type and easy to understand.

Basic Syntax for Two Columns

If cell A2 contains "John" and cell B2 contains "Doe," you want the result to be "John Doe." Using the formula =A2&B2 would result in "JohnDoe." To add a space, you must include a text string consisting of a space enclosed in double quotes.

The Formula: =A2 & " " & B2

Merging Multiple Columns and Custom Delimiters

You are not limited to just two columns. You can string together as many cells as needed. For example, if you are creating a full address from City (A2), State (B2), and Zip Code (C2), you might want a comma and a space as your delimiter.

The Formula: =A2 & ", " & B2 & " " & C2

In this example:

& ", " adds a comma and a space after the city.
& " " adds a space before the zip code.

Professional Tips for the Ampersand Method

In my experience managing large CRM exports, the Ampersand method is the "gold standard" for quick data cleanup. However, it has one major drawback: it does not handle empty cells gracefully. If you have a middle name column where some cells are blank, the Ampersand method might leave you with double spaces (e.g., "John Doe").

If your dataset is perfectly filled, use the Ampersand. If you have "holey" data with missing values, consider the TEXTJOIN function discussed later in this article.

Using Flash Fill for a Formula-Free Approach

Flash Fill is one of Excel’s most powerful AI-driven features. It recognizes patterns as you type and automatically fills the rest of the column for you. This is the best method for users who prefer not to deal with complex formulas.

How to Trigger Flash Fill

Type the Pattern: In the column next to your data, manually type how you want the first row to look. For instance, if A2 is "Apple" and B2 is "iPhone," type "Apple iPhone" in C2.
Provide a Second Example: Sometimes Excel needs a little more help. Type the desired result for C3 as well.
Use the Shortcut: Press Ctrl + E on your keyboard. Excel will instantly scan the columns to the left and populate the entire column based on your pattern.

Why Professionals Use Flash Fill

Flash Fill is more than just a merger; it can transform data. If you type "J. Doe" (taking the first initial from A2 and the full last name from B2), Flash Fill will apply that specific logic to thousands of rows instantly.

The Limitations of Flash Fill

While fast, Flash Fill is static. Unlike a formula, the data does not update if you change the original columns. If you correct a typo in Column A, the Flash Fill result in Column C will remain unchanged. Always use Flash Fill at the very end of your data preparation process for this reason.

The Power of the TEXTJOIN Function

Available in Excel 2019 and Microsoft 365, TEXTJOIN is the most sophisticated way to combine columns. It solves the biggest headache of the Ampersand method: handling empty cells.

Understanding the TEXTJOIN Syntax

The syntax for TEXTJOIN is: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

Delimiter: The character you want between your items (e.g., ", " or " ").
Ignore_empty: A logical value. Setting this to TRUE tells Excel to skip any cells that are blank.
Text1, Text2: The ranges or cells you want to merge.

Example: Merging an Address List

Imagine you have columns for Street 1, Street 2, City, and State. Often, "Street 2" (apartment numbers) is empty.

The Formula: =TEXTJOIN(", ", TRUE, A2:D2)

If A2 is "123 Main St," B2 is empty, C2 is "New York," and D2 is "NY," the result will be: "123 Main St, New York, NY"

If you used the Ampersand method, you would have ended up with "123 Main St, , New York, NY" which looks unprofessional and requires manual fixing.

CONCAT and CONCATENATE: The Old Guard

For many years, CONCATENATE was the only official function for joining text. In newer versions of Excel, it has been replaced by CONCAT.

CONCATENATE vs. CONCAT

CONCATENATE: Requires you to select each cell individually. It does not support ranges. For example: =CONCATENATE(A2, " ", B2).
CONCAT: Supports ranges. You can type =CONCAT(A2:D2), and it will smash all the data together. However, CONCAT does not allow you to specify a delimiter between items in a range, which makes it less useful than TEXTJOIN for most tasks.

If you are working on a legacy spreadsheet created in Excel 2010 or 2013, you will likely see CONCATENATE. If you are starting a new project in 2024, stick to TEXTJOIN or the Ampersand.

How to Combine Columns Without Losing Formatting

One of the most common issues users face is losing the formatting of numbers, dates, or currency when merging columns.

The Problem with Merging Dates

If Column A has the name "John" and Column B has the date "01/01/2024," using =A2 & " " & B2 will result in "John 45292". This is because Excel stores dates as serial numbers. To fix this, you must use the TEXT function within your merger.

The Solution: The TEXT Function

To merge a name and a date properly, use this formula: =A2 & " was born on " & TEXT(B2, "mm/dd/yyyy")

Similarly, for currency: =A2 & " owes " & TEXT(C2, "$#,##0.00")

By wrapping the numeric cell in the TEXT function, you tell Excel exactly how the data should look in its final combined form.

Using Power Query for Massive Datasets

When dealing with hundreds of thousands of rows, formulas can significantly slow down your Excel workbook. Every time you change a cell, Excel recalculates every formula, which leads to the dreaded "Calculating (4 threads): 60%" lag.

For big data, Power Query is the professional choice.

Steps to Merge Columns in Power Query:

Select Your Data: Click anywhere in your data range and go to the Data tab > From Table/Range.
Open the Editor: This opens the Power Query Editor window.
Select Columns: Hold the Ctrl key and click the headers of the columns you want to combine.
Merge: Right-click on one of the headers and select Merge Columns.
Choose Delimiter: Select a space, comma, or semicolon from the dropdown.
Load Back to Excel: Click Close & Load.

Power Query is superior for large datasets because it processes the data outside of the standard grid and only updates when you click "Refresh."

How to Remove Formulas and Keep the Combined Values

Whether you use &, TEXTJOIN, or CONCAT, the resulting column is dependent on the original source data. If you delete Column A or Column B, your combined column will show a #REF! error.

To prevent this, you must convert the formulas into static values.

The "Paste Values" Workflow

Highlight the column containing your formulas.
Copy the column (Ctrl + C).
Right-click on the same column.
Under Paste Options, select the icon with the numbers "123" (Paste Values).

Now, the formulas are gone, and only the text remains. You can now safely delete the original columns or move the merged data to another workbook.

Best Practices for Data Integrity

When I design spreadsheets for high-stakes business environments, I follow these three rules to ensure data integrity:

Keep a Backup: Never delete your source columns until you are 100% sure the merged data is correct and you have applied "Paste Values."
Check for Leading/Trailing Spaces: Sometimes your source data has hidden spaces. Use the TRIM function to clean them: =TEXTJOIN(" ", TRUE, TRIM(A2), TRIM(B2)).
Consistency is Key: If you are merging names, decide on a format (First Last vs. Last, First) and stick to it throughout the entire workbook.

Frequently Asked Questions (FAQ)

How do I combine columns with a line break?

If you want the combined text to appear on separate lines within a single cell, use CHAR(10) as your delimiter. For example: =A2 & CHAR(10) & B2. Note: You must enable "Wrap Text" on the cell to see the line break.

Can I merge columns in Excel without using formulas?

Yes, use Flash Fill (Ctrl + E). It is the fastest non-formula method to merge adjacent columns based on a pattern you provide.

Why is my merged column showing a #VALUE! error?

This usually happens if you are trying to use a function like TEXTJOIN on a version of Excel that doesn't support it (pre-2019), or if there is a circular reference where your formula is trying to include the cell it is sitting in.

How can I merge columns but keep the original formatting?

Formulas generally strip away formatting like bold or text color. To keep the look of the data, you would need to use a VBA script or manually re-apply the formatting after using Paste Values.

Summary

Combining columns in Excel is a versatile skill that can be approached in several ways:

Use the Ampersand (&) for quick, daily tasks.
Use Flash Fill for one-off merges where you don't want formulas.
Use TEXTJOIN for professional-grade lists with potential empty cells.
Use Power Query for enterprise-level datasets that require high performance.
Always remember to Paste Values if you intend to delete your source columns.

By mastering these techniques, you can transform messy spreadsheets into organized, actionable data in a matter of seconds.