Merge Two Columns Data In Excel

Merging data from two columns in Excel is a fundamental skill that transforms messy spreadsheets into organized, analysis-ready datasets. Whether combining first and last names, joining address lines, or concatenating product codes with categories, mastering this task saves hours of manual typing and reduces human error. This guide explores every reliable method—from modern dynamic array formulas to classic Flash Fill—ensuring you can choose the right tool for your specific version of Excel and data complexity No workaround needed..

Understanding the Difference: Merge Cells vs. Merge Data

Before diving into techniques, it is critical to distinguish between two commonly confused actions. On the flip side, Merging cells (using the Merge & Center button on the Home tab) combines adjacent cells into a single larger cell visually. It keeps only the upper-left value and discards the rest. This is strictly a formatting feature The details matter here. Practical, not theoretical..

Merging data (concatenation), however, joins the text content of two or more cells into a new cell while preserving the original source columns. This is a data manipulation task. For almost all professional workflows—mail merges, database imports, or reporting—you want to merge data, not cells.

Method 1: The TEXTJOIN Function (Excel 2019 and Microsoft 365)

For modern Excel users, TEXTJOIN is the gold standard. It handles delimiters (spaces, commas, dashes) elegantly and ignores empty cells automatically, preventing awkward double spaces in your results.

Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

Practical Example: Assume Column A contains First Names and Column B contains Last Names. You want "John Doe" in Column C And it works..

1. Click cell C2.
2. Enter the formula: =TEXTJOIN(" ", TRUE, A2, B2)
   • " " inserts a space between names.
   • TRUE tells Excel to skip empty cells (useful if a middle name column is missing).
   • A2, B2 are the cells to combine.
3. Press Enter and double-click the fill handle to apply down the column.

Why it wins: Unlike older methods, TEXTJOIN allows you to select a range (e.g., A2:D2) rather than clicking individual cells, making it perfect for merging five or ten columns at once.

Method 2: The CONCAT Function (Excel 2019 and Microsoft 365)

CONCAT is the modern replacement for the legacy CONCATENATE function. It supports range references, which CONCATENATE does not. Even so, it lacks a built-in delimiter argument, meaning you must manually insert separators Practical, not theoretical..

Syntax: =CONCAT(text1, [text2], ...)

Practical Example: To combine A2 and B2 with a comma and space: =CONCAT(A2, ", ", B2)

To combine a range A2:D2 without separators: =CONCAT(A2:D2)

Best Use Case: Use CONCAT when you need to join a range of cells without a delimiter, or when building complex strings where delimiters change between specific cells (e.g., "Name: John | Age: 30") Easy to understand, harder to ignore..

Method 3: The Ampersand (&) Operator (All Versions)

The ampersand is the universal "glue" in Excel. It works in every version ever released, from Excel 97 to the current Microsoft 365. It requires no function syntax knowledge, making it the fastest method for simple, one-off joins.

Practical Example: =A2 & " " & B2

Pros:

• Instant typing, no function wizard needed.
• Universal compatibility.
• Easy to read for simple combinations (2–3 cells).

Cons:

• Becomes unwieldy with many cells: =A2 & " " & B2 & " " & C2 & " " & D2 ...
• No "ignore empty" logic. If B2 is blank, you get "John Doe" (double space). You must wrap cells in IF statements to clean this up: =A2 & IF(B2="",""," " & B2).

Method 4: Flash Fill (Excel 2013 and Later)

Flash Fill is Excel’s pattern-recognition engine. Which means it requires zero formulas. It watches you type the desired result in the first row and instantly predicts the rest of the column.

Step-by-Step Workflow:

1. In Column C (next to your data), manually type the combined result for Row 2 (e.g., type "John Doe").
2. Press Enter to move to C3.
3. Type the second result (e.g., "Jane Smith").
4. Excel usually displays a gray preview list down the column. Press Ctrl + E (or click Data > Flash Fill) to accept.
5. If the preview doesn't appear, type the third row, then press Ctrl + E.

When to use Flash Fill:

• One-time cleanup tasks where you don't want a permanent formula column.
• Complex formatting logic that is hard to write in a formula (e.g., "Doe, John (JD)").
• Extracting or reformatting data (splitting columns works similarly).

Limitations: Flash Fill creates static text. If Column A or B changes later, Column C will not update. It also struggles if the pattern is ambiguous across rows.

Method 5: Power Query (Get & Transform) – For Large Datasets & Automation

If you merge columns weekly, monthly, or on datasets exceeding 50,000 rows, Power Query is the professional choice. It creates a repeatable, recorded script (query) that refreshes with one click.

Workflow:

1. Select your data range > Data Tab > From Table/Range.
2. In the Power Query Editor, hold Ctrl and click the headers of the columns to merge (e.g., FirstName, LastName).
3. Right-click a selected header > Merge Columns.
4. Choose a Separator (Space, Comma, Custom).
5. Name the new column (e.g., FullName) > OK.
6. Home > Close & Load to dump the clean table back into a new sheet.

Key Advantage: The next time source data updates, simply right-click the output table > Refresh. No dragging formulas, no Flash Fill re-runs. It also handles millions of rows without slowing down the workbook calculation engine.

Method 6: Legacy CONCATENATE Function (Pre-2019 Compatibility)

If you are stuck on Excel 2016 or older (or sharing files with users on ancient versions), CONCATENATE is your only function option besides the ampersand.

Syntax: =CONCATENATE(text1, [text2], ...)

Limitation: You cannot select a range like A2:D2. You must click every single cell: =CONCATENATE(A2, " ", B2, " ", C2). It is limited to 255 arguments (roughly 8,192 characters in older versions). Microsoft keeps it only for backward compatibility; avoid it in new workbooks.

Advanced Scenarios & Pro Tips

Adding Line Breaks Inside a Cell

To create a mailing label format (Name on line 1, Address on line 2), use CHAR(10) as the delimiter and enable Wrap Text (Alt+H+W).

Formula: `=TEXTJOIN(CHAR(10), TRUE

Completing the Line‑Break Formula

To generate a multi‑line value such as:

John Doe
123 Main St
Anytown, USA

you can finish the TEXTJOIN expression that was started:

=TEXTJOIN(CHAR(10), TRUE, A2, " ", B2, " ", C2)

• CHAR(10) – the ASCII code for a line break (carriage return).
• TRUE – tells Excel to ignore any empty cells in the range.
• A2, " ", B2, " ", C2 – the source cells and the static spaces that separate them.

After entering the formula, select the cell, press Ctrl + 1, go to Alignment → Wrap Text. The cell will now display each segment on its own line.

If you need a different delimiter (e.g., CHAR(13) for a hard return on macOS), simply replace CHAR(10) Most people skip this — try not to..

Extending TEXTJOIN for Conditional Data

You can make the line‑break formula smarter by wrapping it in an IF or IFERROR to handle missing information:

=IFERROR(
    TEXTJOIN(CHAR(10), TRUE,
        A2, " ",
        IF(B2<>"", CONCATENATE(" ", B2), ""),
        IF(C2<>"", CONCATENATE(" ", C2), "")
    ),
    "N/A"
)

• The IF checks suppress extra spaces when a cell is blank.
• IFERROR catches any unexpected issues and returns “N/A” (or any placeholder you prefer).

Using TEXTJOIN with Ranges

When you have more than three source columns, you can pass a range to TEXTJOIN instead of listing each cell:

=TEXTJOIN(CHAR(10), TRUE, A2:D2)

This works because TEXTJOIN accepts both individual values and ranges. It also respects the TRUE flag, which automatically skips any empty cells within the range.

Combining TEXTJOIN with Other Functions

• Dynamic arrays: Pair TEXTJOIN with FILTER or UNIQUE to create on‑the‑fly labels from filtered data.

  =TEXTJOIN(" ", TRUE, FILTER(A2:D2, (E2:E100="Active")))
  

• Error‑proof merging: Use XLOOKUP to pull related fields before feeding them into TEXTJOIN.

  =TEXTJOIN(", ", TRUE,
      XLOOKUP(A2, Table[ID], Table[FirstName]),
      XLOOKUP(A2, Table[ID], Table[LastName]))
  

When to Choose One Method Over Another

Pro Tips & Best Practices

1. Named ranges – Define a range like FullName_Range and refer to it in formulas (=TEXTJOIN(" ", TRUE, FullName_Range)). This makes updates easier.
2. Error handling – Always wrap volatile functions (INDIRECT, OFFSET) or external data pulls with `IF