Concept
Which Excel version?
| Tool | Works in |
|---|---|
& operator |
every version |
CONCAT, TEXTJOIN |
Excel 2019 and later |
TEXTSPLIT |
Microsoft 365, Excel 2024, Excel for the web |
1. & operator — the simplest join
First name in A2 (Rahul), last name in B2 (Sharma):
=A2 & " " & B2 → Rahul Sharma
Add the space yourself as " ".
2. CONCAT — join a range
=CONCAT(A2:C2)
Joins every cell with no separator. It replaces the old CONCATENATE, which could not take a range.
3. TEXTJOIN — join with a separator
=TEXTJOIN(delimiter, ignore_empty, text1, ...)
Names in A2:A5, one of them blank:
=TEXTJOIN(", ", TRUE, A2:A5) → Rahul, Neha, Ravi
TRUE skips the blank cell, so you don't get Rahul, , Neha.
4. TEXTSPLIT — one cell into many
A2 contains Rahul,Sharma,Delhi:
=TEXTSPLIT(A2, ",") → Rahul | Sharma | Delhi
The result spills into 3 columns. To split into rows instead, use the third argument:
=TEXTSPLIT(A2, , ",")
If TEXTSPLIT is unavailable, use Data → Text to Columns for the same job (it's not a formula, so it won't update).
5. Numbers and dates inside text — TEXT
Joining a date gives an ugly serial number:
="Invoice date: " & A2 → Invoice date: 46296
Format it with TEXT:
="Invoice date: " & TEXT(A2, "dd-mmm-yyyy") → Invoice date: 01-Oct-2026
="Total: ₹" & TEXT(B2, "#,##0") → Total: ₹125,000
Common format codes: dd-mm-yyyy, mmm-yy, dddd (day name), 0.00, #,##0.
See Microsoft’s TEXTSPLIT documentation for supported versions.
Common mistakes
Forgetting the space in A2&" "&B2. Joining dates or currency without TEXT. Using TEXTSPLIT in a file that people will open in Excel 2019/2021 (#NAME?).