Most Excel trainers still teach CONCATENATE like it’s sacred scripture. It’s not. Microsoft officially deprecated it in 2016—and if you’re typing =CONCATENATE(A1,B1) instead of =A1&B1 or =TEXTJOIN(“”,TRUE,A1:B1), you’re adding unnecessary keystrokes and confusion for zero benefit.
Quick Answer
To append one string to another in Excel, use the ampersand (&) operator: =A1&" - "&B1 appends cell A1, a hyphen with spaces, and B1. For multiple cells or handling blanks, TEXTJOIN is cleaner and more reliable than CONCAT or CONCATENATE.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| & (Ampersand) | Type =A1&" "&B1 in any blank cell | Simple two- or three-cell joins with fixed delimiters | No built-in blank-skipping; errors if any referenced cell is #N/A |
| TEXTJOIN | =TEXTJOIN(" | ",TRUE,A1:C1) — TRUE ignores blanks | Merging 3+ cells, ignoring blanks, consistent delimiters | Not available in Excel 2013 or earlier |
| CONCAT | =CONCAT(A1:C1) — no delimiter, no blank control | Legacy compatibility; quick bulk text stacking | Ignores delimiters and blanks entirely—often creates run-on text like "SarahChenAcmeCorp2024-03-15" |
| Paste Special → Add | Copy delimiter string → select target cells → Alt+E+S+A → Enter | Appending same suffix (e.g., "-Q3") to dozens of existing values | Only works with numbers unless formatted as text first; overwrites originals |
| Flash Fill (Ctrl+E) | Type first result manually → select next cell → Ctrl+E | Irregular patterns (e.g., "ID-" + number + "-v2") where formulas get messy | Unreliable across large datasets; breaks if pattern changes mid-column |
Method 1 Deep Dive
Let’s say your sales team pasted raw data from a CRM into columns A–C:
| A | B | C | D (Result) |
|---|---|---|---|
| Sarah Chen | Acme Corp | 2024-03-15 | Sarah Chen | Acme Corp | 2024-03-15 |
| James Rivera | Nexus Labs | 2024-03-16 | James Rivera | Nexus Labs | 2024-03-16 |
| Maya Patel | 2024-03-17 | Maya Patel | | 2024-03-17 |
The & method gives you =A2&" | "&B2&" | "&C2—but notice row 4? That empty B2 creates a double pipe: "Maya Patel | | 2024-03-17". Annoying. Fix it by switching to TEXTJOIN: =TEXTJOIN(" | ",TRUE,A2:C2). The TRUE argument skips blanks, so row 4 becomes "Maya Patel | 2024-03-17" — clean and accurate. Type that in D2, then double-click the fill handle to copy down.
Pro tip: TEXTJOIN tolerates ranges *and* individual cells. So =TEXTJOIN(", ",TRUE,A2,B2,"USD",C2) works fine — no need to cram everything into one range.
Method 2 Deep Dive
Here’s the surprise most tutorials ignore: You can append text to existing values *in place*, without formulas — using Paste Special. Say column E holds product codes like "PRD-7821", and your manager just asked you to add "-ARCHIVE" to every one.
Step 1: In an unused cell (say G1), type "-ARCHIVE".
Step 2: Copy G1 (Ctrl+C).
Step 3: Select E2:E10 (your product codes).
Step 4: Press Alt+E+S+A — that’s the keyboard sequence for Paste Special → Add.
Step 5: Hit Enter.
Yes — Excel treats text as numbers here, but only because it’s performing string addition. It works because Excel silently converts text to numbers during arithmetic operations… and when conversion fails (like "PRD-7821" + "-ARCHIVE"), it falls back to concatenation. This trick only works if the original cells are formatted as Text *before* you paste — otherwise you’ll get #VALUE! errors. Right-click E1 → Format Cells → Text first. Then try Alt+E+S+A again.
This method is brutal on large datasets — but invaluable when you need to update source data directly and can’t add helper columns. I used it last week to tag 200+ PO numbers before uploading to SAP. No formulas. No new columns. Just one hotkey combo.
Cheat Sheet
| Task | Formula / Shortcut | Notes |
|---|---|---|
| Join A1 + space + B1 | =A1&" "&B1 |
Fastest for simple cases; no function overhead |
| Join A1:C1, skip blanks | =TEXTJOIN(" • ",TRUE,A1:C1) |
Works in Excel 2016+, Office 365, Excel for Web |
| Append "-OLD" to E2:E100 | Copy "-OLD" → select E2:E100 → Alt+E+S+A | Format E:E as Text first — critical step |
| Preview join before typing | Type first result manually in F2 → select F3 → Ctrl+E | Great for mixed formats (e.g., "ID" + 4-digit number + "-rev") |
| Force text mode on numbers | =TEXT(A1,"00000")&"-FINAL" |
Prevents Excel from dropping leading zeros during append |
