How to Combine Two Columns in Excel

Method 1: Ampersand Operator (Fastest)

=A2&" "&B2

The ampersand (&) operator joins two values. Put the separator between them — a space, a comma, a hyphen, or nothing — as a quoted string. This is the quickest formula for straightforward two-column joins.

Examples:

• Full name: =A2&" "&B2 → "John Smith"
• City and state: =A2&", "&B2 → "Chicago, IL"
• Category and ID: =A2&"-"&B2 → "ELEC-4521"
• No separator: =A2&B2 → "JohnSmith"

Limitation: If one of the cells is blank, you get a leading or trailing separator. For example, if B2 is empty, =A2&" "&B2 returns "John " with a trailing space. Use TEXTJOIN to handle blank cells cleanly.

Method 2: CONCAT Function

=CONCAT(A2," ",B2)

CONCAT is the modern replacement for the older CONCATENATE function. It is easier to read when joining several pieces and accepts ranges as well as individual cells:

=CONCAT(A2:C2)

This joins all values in A2 through C2 with no separator. To add a separator between each, use TEXTJOIN instead. CONCAT does not skip blank cells — a blank in the middle produces adjacent separators.

Method 3: TEXTJOIN (Best for Blank Cell Handling)

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

TEXTJOIN is the most flexible joining function. The syntax is:

=TEXTJOIN(delimiter, ignore_empty, value1, value2, ...)

• delimiter: The character or string to place between values. Use " " for a space, ", " for comma-space, "-" for a hyphen.
• ignore_empty: Set to TRUE to skip blank cells automatically. This prevents double spaces or double separators when a column is empty for some rows.
• value1, value2, ...: The cells or ranges to join.

TEXTJOIN with a Range

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

This joins all four columns with comma-space separators, automatically skipping any that are blank. This is ideal for addresses, tag lists, category paths, and any field where some entries may be partially filled.

Practical Examples

• Full name (skip middle name if blank): =TEXTJOIN(" ",TRUE,A2,B2,C2)
• Address line (skip apartment if blank): =TEXTJOIN(", ",TRUE,A2,B2,C2,D2)
• Tags: =TEXTJOIN("; ",TRUE,A2:F2)

Method 4: CONCATENATE (Legacy)

=CONCATENATE(A2," ",B2)

CONCATENATE is the original joining function from earlier versions of Excel. It still works in all Excel versions but has two limitations compared to CONCAT and TEXTJOIN: it accepts individual cells only (not ranges), and it does not skip blank cells. Use TEXTJOIN in new workbooks unless you need compatibility with very old Excel versions.

Method 5: Flash Fill (No Formula)

Flash Fill is a quick one-time option when you do not need a live formula:

1. In the column next to your data, type the desired combined result for the first row. For example, if A2 is "John" and B2 is "Smith", type "John Smith" in C2.
2. Press Enter to confirm, then start typing the result for C3.
3. Excel detects the pattern and shows a preview of the filled values. Press Enter or Ctrl + E to accept.

Flash Fill produces values, not formulas, so there is no formula dependency. But if the source data changes, Flash Fill results do not update automatically. Use formulas when the source data is live.

Method 6: Power Query (For Repeatable or Large-Scale Joins)

Power Query is the right tool when you need to combine columns as part of a repeatable import process or when the dataset is large and complex:

1. Load the data into Power Query via Data > From Table/Range.
2. Select both columns you want to combine while holding Ctrl.
3. Right-click and choose Merge Columns.
4. Choose a separator and give the new column a name.
5. Click Close & Load to write the result back to Excel.

The advantage of Power Query is that when new data arrives, you refresh the query instead of re-entering formulas or re-running Flash Fill. This is the best approach for regular CSV imports, database exports, or data that updates weekly.

How to Keep the Combined Result Without the Formula

After creating the combined column with a formula, you may want to convert the formula results to plain text values — especially before deleting the source columns. To do this safely:

1. Select the combined formula column.
2. Copy it with Ctrl + C.
3. Right-click in the same location and choose Paste Special > Values (or press Alt + E, S, V).
4. The column now contains plain text. The formulas are gone.
5. Now you can safely delete the original source columns.

Never delete source columns while formulas still reference them. The formulas will break and show #REF! errors.

Combining a Date with Text

Dates stored as Excel dates (serial numbers) do not display correctly when concatenated with text. Excel treats them as numbers. Use TEXT to format the date before joining:

=A2&" - "&TEXT(B2,"MMM DD, YYYY")

Replace "MMM DD, YYYY" with whatever date format you need. Common formats:

• "YYYY-MM-DD" → "2026-06-25"
• "MM/DD/YYYY" → "06/25/2026"
• "DD MMM YYYY" → "25 Jun 2026"

Combining a Number with Text

Numbers also need TEXT formatting when joined with text if you want to control the display:

="Revenue: "&TEXT(B2,"$#,##0")

Without TEXT, ="Revenue: "&B2 might display as "Revenue: 45000" without any currency formatting. Use TEXT to apply the same number format the cell would normally display.

Common Mistakes

• Forgetting the separator: =A2&B2 joins with no space, producing "JohnSmith" instead of "John Smith".
• Deleting source columns before pasting values: This breaks all formulas that reference those columns.
• Combining numbers when they should stay numeric: Joining a quantity column with a unit column produces text. If the result needs to be summed or calculated, keep the numeric column separate.
• Not handling blank cells: Using ampersand or CONCAT when some rows have blank cells produces double spaces or orphaned separators. Use TEXTJOIN with TRUE for the ignore_empty argument.
• Using CONCATENATE in new workbooks: TEXTJOIN handles blanks better and is available in Excel 2019, Excel 365, and all modern versions.

Frequently Asked Questions

How do I combine first name and last name in Excel?

Use =TEXTJOIN(" ",TRUE,A2,B2) or =A2&" "&B2. TEXTJOIN is better if some rows have only a first name and no last name, because the TRUE argument skips the blank second column and avoids a trailing space.

How do I combine columns and add a line break between them?

Use CHAR(10) as the line break character and make sure the cell has Wrap Text enabled:

=A2&CHAR(10)&B2

Without Wrap Text, the line break is invisible and the values appear on the same line.

Can I combine more than two columns at once?

Yes. TEXTJOIN supports up to 252 arguments and accepts ranges: =TEXTJOIN(", ",TRUE,A2:F2) joins all six columns with comma-space separators. The ampersand requires you to list each column individually: =A2&", "&B2&", "&C2&", "&D2.

How do I split a combined column back into two columns?

Use Data > Text to Columns to split on a delimiter, or use LEFT, RIGHT, MID, and FIND formulas to extract parts. For structured data like "First Last", =LEFT(A2,FIND(" ",A2)-1) extracts the first name and =MID(A2,FIND(" ",A2)+1,100) extracts the last name.