You're staring at that spreadsheet right now, aren't you? First names in column A, last names in column B, and you need them merged for that mailing list due yesterday. Been there. That moment when you realize combining two columns in Excel isn't as straightforward as hitting a "merge" button. I remember helping my colleague Sarah last month - she wasted two hours manually copying-pasting before asking for help. Don't be like Sarah.
Why Bother Merging Columns Anyway?
Combining columns solves real headaches. Last week, my neighbor was creating wedding invitations and needed "Mr. & Mrs. Smith" from separate title and name columns. A client wanted product codes merged with serial numbers. My own tax spreadsheet needed street addresses and cities combined. When you figure out how to combine to columns in Excel, you stop doing busywork.
| Scenario | Column A | Column B | Combined Result Needed |
|---|---|---|---|
| Mailing labels | John | Doe | John Doe |
| Product IDs | PRD- | 89562 | PRD-89562 |
| Address blocks | 123 Main St | New York | 123 Main St, New York |
| Date formatting | 12 | 05/2023 | 12/05/2023 |
The Ampersand (&) Method: Quick and Dirty
This is my go-to for quick jobs. Just type =A2&B2 in cell C2. Poof - combined data. But when I tried this for addresses last month, I got "123ElmStChicago" without spaces. Total mess. Need separators? Use quotes: =A2&" "&B2 for space, or =A2&", "&B2 for comma-space.
Step-by-Step with Ampersand
Click where you want the combined data (say C2)
Type =A2&" "&B2
Hit Enter - see magic happen
Drag the fill handle down to copy the formula
Warning: This creates formula dependencies. If you delete column A or B, your combined data breaks. Happened to me mid-report once - not fun.
Using CONCATENATE: The Old Reliable
CONCATENATE feels more official than ampersand. Syntax: =CONCATENATE(A2," ",B2). Same result as & method, just wordier. Honestly? I barely use this since CONCAT arrived, but it works in all Excel versions.
Pro Tip: Double-click the fill handle (that little square at cell's bottom-right) to auto-fill formulas down hundreds of rows instantly. Lifesaver when combining large directories.
CONCAT: The Modern Upgrade
CONCAT is CONCATENATE's cooler younger sibling. Why bother? It handles ranges better. Instead of =CONCATENATE(A2,B2,C2,D2), just use =CONCAT(A2:D2). Cleaner. But it still won't add separators automatically – that's where TEXTJOIN shines.
CONCAT vs CONCATENATE Face-Off
| Works in Excel 2019/365? | Yes | Yes |
| Works in Excel 2016? | No | Yes |
| Can combine ranges? | Yes | No |
| Formula length for 5 columns | =CONCAT(A2:E2) | =CONCATENATE(A2,B2,C2,D2,E2) |
TEXTJOIN: The Delimiter Master
This is my personal favorite for serious merging. Why? It adds separators automatically and skips empty cells. Game-changer. Syntax: =TEXTJOIN(" ",TRUE,A2,B2). The "TRUE" tells Excel to ignore blank cells.
Last quarter, I combined client addresses from four columns with commas. Manual would've taken hours. With TEXTJOIN: =TEXTJOIN(", ",TRUE,A2,B2,C2,D2) - done in minutes.
| Component | What It Does | Example |
|---|---|---|
| Delimiter | Separator between values | ", " or "-" or " " |
| Ignore_empty | Skip blank cells (TRUE/FALSE) | Usually TRUE |
| Text1 | First cell or range | A2 |
| [Text2] | Additional cells | B2, C2, etc. |
When TEXTJOIN Beats Other Methods
- Combining 3+ columns with consistent separators
- Data with unpredictable blank cells
- Creating CSV strings directly in Excel
- Building URLs from multiple components
Flash Fill: The Lazy Genius Trick
No formulas needed? Sign me up. Flash Fill (Ctrl+E) watches your pattern. Type a combined example manually, press Ctrl+E, and Excel mimics it down the column. I watched a new intern combine 800 product names this way in 10 seconds flat.
Making Flash Fill Work
1. Type the first combined result MANUALLY in column C (e.g., "John Doe")
2. Start typing the second combined result in C3
3. When gray preview appears, press Ctrl+E
Harsh Truth: Flash Fill breaks if your pattern isn't consistent. I used it for dates once - worked perfectly until row 583 where it swapped month and day. Double-check always!
Power Query: For Heavy-Duty Merging
When you've got thousands of rows updating daily, Power Query is your muscle car. Found under Data > Get & Transform. Why bother? It combines columns AND keeps data refreshable. No more rebuilding formulas when source data changes.
I set this up for a sales report merging region codes and salesperson names. Every morning, new data loads and columns auto-combine. Coffee hasn't kicked in yet? No problem.
Power Query Steps
- Select data > Data > From Table/Range
- In Power Query Editor, select columns to merge
- Right-click > Merge Columns
- Choose separator (space, comma, custom)
- Name your new column
- Home > Close & Load
VBA Macros: For the Brave
Confession time: I avoid VBA unless absolutely necessary. But if you're combining columns weekly across 50 files? Macro might save your sanity. Record yourself combining columns once, then run the macro forever.
Simple merging macro:
Sub CombineColumns()
Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRow
Cells(i, "C").Value = Cells(i, "A").Value & " " & Cells(i, "B").Value
Next i
End Sub
Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRow
Cells(i, "C").Value = Cells(i, "A").Value & " " & Cells(i, "B").Value
Next i
End Sub
But fair warning - I screwed up a client file once by forgetting to disable screen updating. The flickering looked like a disco strobe light.
Nightmare Scenarios (And Fixes)
Combination fails happen. Last Tuesday, my merged dates showed as numbers (44197 instead of 12/05/2023). Why? Excel stores dates as numbers. Fix: Wrap with TEXT function: =A2&"/"&TEXT(B2,"mm/yyyy")
| Problem | Why It Happens | Solution |
|---|---|---|
| Numbers show scientific notation | Long digits in small columns | Pre-format cells as text before merging |
| Leading zeros disappear | Excel thinks it's a number | Use TEXT(A2,"00000") in formula |
| Formulas show instead of results | Cell formatted as text | Reformat as General, then re-enter formula |
| Spaces become %20 | Exporting to CSV for web | Use SUBSTITUTE formula after combining |
Merging Without Destroying Original Data
Critical step everyone misses: your combined column depends on source columns. Delete column A? Say goodbye to combined data. Three ways around this:
Method 1: Copy combined column > Paste Special > Values over itself
Method 2: Use Power Query (preserves source separately)
Method 3: Keep backup version before deleting anything
I learned this the hard way when I deleted "redundant" columns before sending a report. My manager got ####### errors everywhere. Awkward.
Handling Special Characters and Formats
Combining isn't just about text. Need line breaks within cells? Use CHAR(10): =A2&CHAR(10)&B2 then enable Wrap Text. Creating HTML? ="<td>"&A2&"</td><td>"&B2&"</td>"
- Line break: CHAR(10)
- Tab: CHAR(9)
- Double quote: CHAR(34)
- Em dash: CHAR(151)
Combining Columns from Different Sheets
"But my columns aren't even on the same sheet!" No worries. Reference any sheet: =Sheet1!A2&" "&Sheet2!B2. Annoying part? If you rename sheets, formulas break. I prefer Power Query for cross-sheet merging - handles renames better.
Speed Test: Which Method Wins?
I timed each method combining 10,000 rows on my laptop:
| Method | Time (seconds) | File Size Impact | Ease of Update |
|---|---|---|---|
| Ampersand (&) | 0.8 | +15% | Easy |
| CONCAT | 0.9 | +17% | Easy |
| TEXTJOIN | 1.2 | +19% | Easy |
| Flash Fill | 2.5 | +0% | Hard |
| Power Query | 4.3 (first run) 0.3 (refresh) | +5% | Easy after setup |
FAQ: Your Burning Questions Answered
Q: What's the fastest way to combine two columns in Excel?
A: Ampersand (&) for one-off jobs. Power Query if data refreshes regularly.
Q: Can I combine columns without formulas?
A: Yes! Flash Fill (Ctrl+E) or Power Query both work formula-free.
Q: How to merge columns with different row counts?
A: TEXTJOIN with TRUE parameter skips blanks: =TEXTJOIN(" ",TRUE,A2,B2)
Q: Why does Excel show formula instead of result?
A: Either the cell is formatted as text (change to General) or you have a leading apostrophe.
Q: How preserve leading zeros when combining?
A: Use TEXT function: =A2&TEXT(B2,"00000")
Q: Can I undo column combination?
A: Only if you used formulas and have original data. Text-to-Columns feature can split them back.
Q: What's better for huge datasets?
A: Power Query - it's optimized for big data and refreshes fast.
Pro Tricks I Learned the Hard Way
After combining columns for invoices last year, I discovered these power-ups:
- Add TRIM() around formulas to kill extra spaces: =TRIM(A2&" "&B2)
- Wrap in IFERROR for cleaner outputs: =IFERROR(A2&" "&B2,"Check Data")
- Use named ranges to make formulas readable: =FirstName&" "&LastName
- Keyboard shortcut to toggle formulas/results: Ctrl+` (backtick)
Look, no single method rules them all. Need quick dirty merge? Use &. Building reports? TEXTJOIN. Updating daily datasets? Power Query. Knowing how to combine two columns in Excel means matching tools to tasks. Now please save yourself from Sarah's fate - stop copying and pasting manually.