How To Merge Columns Data In Excel

5 min read

Combining information spread across multiple cells is one of the most frequent tasks professionals face when cleaning datasets, preparing mail merges, or building reports. Whether you need to join a first name with a last name, concatenate an address line with a city and zip code, or simply merge text strings with a specific delimiter, Microsoft Excel offers several distinct methods to achieve the result. Understanding the difference between merging cells (a formatting action) and merging data (a data manipulation action) is the critical first step to avoiding data loss.

Understanding the Difference: Merge Cells vs. Merge Data

Before diving into the techniques, it is vital to clarify a common point of confusion. Now, the Merge & Center button on the Home tab merges the physical cells into one larger cell. It keeps only the value in the upper-left cell and discards data in the other selected cells. This is purely a visual formatting tool for headers or titles.

Merging data, often called concatenation, combines the content of multiple cells into a single new cell without deleting the source columns. This preserves your original data integrity while creating a new, unified string. The methods below focus exclusively on merging data content.

Method 1: The Ampersand (&) Operator — The Classic Approach

The ampersand symbol is the original concatenation operator in Excel. It functions exactly like the plus sign for numbers but joins text strings instead. It is universally compatible with every version of Excel ever released.

Syntax: =Cell1 & Cell2 & Cell3...

To add spaces, commas, or other separators, you must include them inside double quotation marks.

Example: If cell A2 contains "John" and B2 contains "Doe":

  1. Click the cell where you want the result (e.g., C2).
  2. Type =A2 & " " & B2.
  3. Press Enter.

The result is "John Doe". Because of that, the space inside the quotes " " acts as the separator. You can chain as many references as needed: =A2 & ", " & B2 & " - " & C2 Surprisingly effective..

Pros: Fast to type, works in all Excel versions, flexible for mixing cell references and hardcoded text. Cons: Formulas become long and hard to read when joining many columns.

Method 2: The CONCAT Function — The Modern Standard

Introduced in Excel 2019 and Microsoft 365, CONCAT replaces the older CONCATENATE function. It accepts range references, meaning you can select a block of cells (e.That said, g. , A2:E2) rather than clicking each cell individually.

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

Example: To merge columns A through E in row 2 with a space delimiter:

  1. Type =CONCAT(A2:E2).
  2. Note: This squishes text together (e.g., "JohnDoeNewYorkNY").
  3. To add delimiters, you must still list cells individually or use a helper column, as CONCAT does not have a built-in delimiter argument.

Pros: Handles ranges efficiently, shorter syntax for contiguous columns without separators. Cons: No native delimiter support; unavailable in Excel 2016 and earlier Worth keeping that in mind..

Method 3: The TEXTJOIN Function — The Power User’s Choice

If you are on Excel 2019 or Microsoft 365, TEXTJOIN is the superior function for merging columns. In real terms, it solves the biggest headache of concatenation: delimiters. It also offers the ability to ignore empty cells automatically Small thing, real impact. Took long enough..

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

  • Delimiter: The character(s) to insert between values (e.g., ", ", " ", "-"). Must be in quotes.
  • Ignore_empty: TRUE (skip blanks) or FALSE (include blanks, creating double delimiters).
  • Text1: The range or cell references.

Example: Merge A2:E2 with a comma and space, skipping empty cells: =TEXTJOIN(", ", TRUE, A2:E2)

If A2="Apple", B2="", C2="Banana", the result is "Apple, Banana" (not "Apple, , Banana") No workaround needed..

Pros: Native delimiter support, ignores empty cells, accepts ranges. Cons: Only available in newer Excel versions.

Method 4: Flash Fill — The No-Formula Magic

Flash Fill is a pattern-recognition engine introduced in Excel 2013. It watches you type an example of the desired output in the adjacent column and instantly fills the rest of the column based on that pattern. It requires zero formulas Not complicated — just consistent..

Steps:

  1. Ensure your source columns are adjacent to an empty column.
  2. In the first row of the empty column, manually type the exact combined result you want (e.g., type "Doe, John" based on A2="John", B2="Doe").
  3. Press Enter to move to the next row.
  4. Type the second example (e.g., "Smith, Jane").
  5. Press Ctrl + E (or go to Data tab > Flash Fill).
  6. Excel detects the pattern ("Last, First") and fills the entire column instantly.

Pros: Extremely fast for one-time tasks, no formula maintenance, handles complex formatting (capitalization, punctuation) intuitively. Cons: Static results. If source data changes, the merged column does not update. You must re-run Flash Fill. It also struggles if the pattern is ambiguous across rows.

Method 5: Power Query — The Enterprise Solution

For large datasets, recurring reports, or data imported from external sources (databases, CSVs, web), Power Query (Get & Transform) is the professional standard. It creates a repeatable, recorded set of steps.

Steps:

  1. Select your data range and press Ctrl + T to create an Excel Table (crucial for dynamic ranges).
  2. Go to Data tab > From Table/Range. The Power Query Editor opens.
  3. Hold Ctrl and click the column headers you want to merge.
  4. Right-click a selected header > Merge Columns.
  5. Choose a Separator (Space, Comma, Custom) and name the New Column Name.
  6. Click OK.
  7. Go to Home > Close & Load to push the result back to a new sheet in Excel.

Pros: Fully dynamic (refresh updates merged column), handles millions of rows, audit trail of steps, separates transformation logic from the worksheet grid. Cons: Steeper learning curve, overkill for simple, one-off tasks on small datasets It's one of those things that adds up..

Method 6: TEXT Function for Formatting Numbers and Dates

A frequent frustration when merging columns involves numbers, dates, or currencies losing their formatting. If A2 is a date 1/15/2024 and B2 is "Report", =A2 & " " & B2 yields 45312 Report (the serial number) Most people skip this — try not to..

Wrap the cell reference in the TEXT function to preserve visual formatting.

Syntax: =TEXT(value, format_text)

Example: =TEXT(A2, "mm/dd/yyyy") & " " & B2 Result: 01/15/2024 Report

Common format codes:

  • Dates: "mm/dd/yyyy", "dd-mmm-yy", "yyyy-mm-dd"
  • Currency: `"$#,##0.
Just Published

Straight from the Editor

Dig Deeper Here

If You Liked This

Thank you for reading about How To Merge Columns Data In Excel. We hope the information has been useful. Feel free to contact us if you have any questions. See you next time — don't forget to bookmark!
⌂ Back to Home