How to Combine Two Columns in Excel: Ampersand, TEXTJOIN, and Flash Fill

Knowing how to combine two columns in excel is an essential data wrangling skill that analysts use in every data preparation project. When customer databases store First Name and Last Name in separate columns, or inventory systems track Item Code and Warehouse ID independently, combining fields into unified strings allows you to generate composite primary keys, format full mailing names, or prepare labels for business intelligence dashboards.

However, many beginner spreadsheet users make the critical mistake of using the ribbon's Merge & Center button, which permanently destroys data in the adjacent column. Professional analysts combine cell contents using formulas and non-destructive transformations.

In this guide, you will learn the four best techniques for combining columns, how to handle dates and numbers without formatting glitches, and how to automate concatenation across thousands of rows. If you are cleaning raw datasets, this tutorial pairs with our guide on how to merge two cells in Excel and our overview of data cleaning in Excel.

How to Combine Two Columns in Excel: The 4 Core Methods

Depending on your version of Excel and your project requirements, you have four distinct methods to combine columns:

1. Ampersand (&) Operator:   =A2 & " " & B2
2. TEXTJOIN Function:        =TEXTJOIN(" ", TRUE, A2:B2)
3. CONCAT Function:          =CONCAT(A2, " ", B2)
4. Flash Fill (No Formula):  Ctrl + E

Method 1: The Ampersand (&) Operator (Universal & Fast)

The ampersand operator is the fastest, most universal way to join text strings in any version of Excel or Google Sheets.

=Cell1 & "delimiter" & Cell2

To combine First Name in A2 with Last Name in B2, inserting a space between them:

=A2 & " " & B2

Argument Breakdown

• A2: The first cell content (e.g., "Priya").
• &: The concatenation operator telling Excel to glue strings together.
• " ": A literal text string containing a single space enclosed in quotation marks.
• B2: The second cell content (e.g., "Sharma").

Result: "Priya Sharma". Double-click the fill handle to copy down the entire column.

Method 2: TEXTJOIN (Best for Multiple Columns & Delimiters)

Available in Excel 2019, Microsoft 365, and Excel for the Web, TEXTJOIN is the most powerful text combination formula in spreadsheets.

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

Example: Combining Address Columns

Suppose columns A through D contain Street, City, State, and PIN code:

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

If the State cell is empty, TEXTJOIN ignores it and produces "MG Road, Bengaluru, 560001" without awkward double commas (", ,").

Method 3: Flash Fill (Ctrl + E) — No Formulas Needed

Flash Fill uses predictive AI to detect patterns in your typing and automatically fills the remaining rows without writing formulas.

1. In cell C2 (next to your two columns), type the desired final result manually (e.g., "Priya Sharma").
2. Press Enter to move down to cell C3.
3. Press Ctrl + E on Windows (or go to Data > Flash Fill on the ribbon).
4. Excel immediately fills all rows in column C matching the pattern.

Handling Special Data Types When Combining Columns

Combining Text with Dates

In Excel, dates are stored internally as numeric serial integers (for instance, September 8, 2026 is stored as 46273). If you combine a customer name with a date using simple concatenation:

="Order by " & A2 & " on " & B2
-- Produces: "Order by Priya on 46273" (Incorrect)

To format the date properly, wrap the date cell in the TEXT() function:

="Order by " & A2 & " on " & TEXT(B2, "dd-mmm-yyyy")
-- Produces: "Order by Priya on 08-Sep-2026" (Clean)

Combining Numbers with Currency or Percentages

Similarly, combining currency numbers requires formatting masks:

=A2 & " - " & TEXT(B2, "$#,##0.00")
-- Produces: "Laptop - $1,250.00"

Common Mistakes When Combining Columns

1. Missing Spaces Between Words

Writing =A2&B2 results in "PriyaSharma" because Excel does not insert spaces automatically. Always include & " " &.

2. Forgetting to Convert Formulas to Static Values

If you combine columns in Column C and then delete the original Columns A and B, Column C will throw #REF! errors. Before deleting source columns, copy Column C and right-click to choose Paste Special > Values (Alt + E + S + V).

Comparison of Column Combination Methods

Real-World Analyst Use Case: Creating Composite Lookup Keys

In transactional databases, order tables frequently lack single unique primary keys. For example, an order might be identified by Store_ID (102) and Register_ID (4). To perform a lookup against this table with VLOOKUP or XLOOKUP, create a helper column that combines both identifiers:

=A2 & "_" & B2
-- Produces: "102_4"

Now, your lookup formula can search for "102_4" unambiguously, eliminating duplicate collisions.

Advanced Methods: How to Combine Two Columns in Excel for Dynamic Data Models

When working across complex analytical models, combining columns requires strict data validation and defensive error handling:

1. Handling Nulls vs Empty Strings: In Excel, a cell containing an empty string ("") behaves differently than a truly blank cell (ISBLANK()). When combining columns with delimiters, =A2 & IF(B2<>" ", " - " & B2, "") prevents dangling punctuation when optional secondary fields are unpopulated.
2. Dynamic Spilled Lookups with Combined Keys: If you need to search against a multi-column key without altering the source table layout, combine columns inside XLOOKUP:

   excel

   Copy code

   =XLOOKUP(A2 & "|" & B2, Table1[Department] & "|" & Table1[Role], Table1[Salary])

   This dynamic in-memory concatenation eliminates the need for physical helper columns in modern Microsoft 365 workbooks.
3. Automated Audit Checks: Always verify row counts after bulk text-joining operations. Applying conditional formatting to detect unexpected double-delimiters ensures your combined export is immediately ready for database loading.

Explore our complete Excel Tutorials hub and practice dynamic spreadsheet workflows on Topfolio Free Excel Course.

Combining Columns via Power Query for Massive Datasets

When dealing with files containing upwards of 200,000 rows, worksheet formulas can degrade workbook recalculation performance. Modern Excel analysts handle bulk transformations using Power Query (Get & Transform Data):

1. Select your data range and click Data > From Sheet/Table to open the Power Query Editor.
2. Hold Ctrl and select both column headers you wish to join in left-to-right order.
3. Navigate to Transform > Merge Columns or Add Column > Merge Columns (if you want to preserve original fields).
4. Select your separator (Space, Comma, Custom delimiter like " - " or " | "), name your new merged column, and click OK.
5. Click Close & Load to output a high-performance structured table back into your workbook. Power Query transformations execute in memory during refresh and require zero worksheet formula overhead.

Checklist for Combining Columns in Production Worksheets

Before exporting your combined dataset to CSV or sending reports to clients, run through this quality audit:

• Confirm whether downstream recipients use Excel 2016 or older; if so, avoid TEXTJOIN and use the universal & operator.
• Check if the source columns contain dates or numbers; wrap them in =TEXT(cell, "format") to prevent raw numeric serial values.
• Freeze combined calculations into static values via Copy > Paste Special > Values (Ctrl + Alt + V, then V) before deleting raw source columns.
• Verify that no cells display #VALUE! from combining mismatched array ranges.

Related Excel Tutorials

• Excel Formulas: The Complete Guide
• XLOOKUP Formula in Excel
• SUM Formula in Excel
• Excel Pivot Tables Guide
• Explore All Guides in the Excel Tutorials Hub