A lot of my work lives in Excel (and Sheets), and for a long time one repetitive chore took up more of my week than anything else: joining a handful of separate cells into a single piece of text. Merging a first and last name into a full name was the common one. Pulling street, city, and state from scattered columns into one clean address line. For years I handled it with ampersands and CONCATENATE, then fixed the output by hand whenever a piece didn’t line up. TEXTJOIN ended that. The same job now takes one formula, and it cleans up the messy edge cases I used to patch manually. It reminded me of the folder of photos I finally sorted with Claude Cowork, a chore that only felt big because I kept doing it the slow way.
The manual way I used to combine text
Ampersand chains and CONCATENATE that broke on the first blank cell
My old method looked simple enough. For a small merge, I would write a formula like =A2 & ” ” & B2 & ” ” & C2, entering every cell reference and separator myself. CONCATENATE did the same thing with different syntax. Neither one scaled. Joining 10 columns meant typing the delimiter 10 times over, and a single missed quote mark broke the entire formula.
Blank cells caused the worst of it. When a middle name or an apartment number was missing, the ampersand chain still dropped in its separator, leaving gaps like “Smith, , Jones” that I had to track down and scrub. Inserting a column in the middle of the data was worse still, since it slid the hardcoded references out of alignment and the formula would return a mangled string with no error to flag it.
I had been coasting on the same habits in Google Sheets, where I overlooked useful features for years out of plain muscle memory. Excel was no different. The slow way worked well enough that I never went looking for a faster one.
Excel finally fixed its biggest data entry problem, and it’s a lifesaver
One click in the Data tab can catch almost all issues.
How TEXTJOIN replaced all of it
Set the delimiter once, pass a whole range
The function takes three required arguments, laid out as =TEXTJOIN(delimiter, ignore_empty, text1, [text2], …). First comes the delimiter, the character or characters you want to sit between each value. A space, a comma and a space, a slash, a line break. The second argument decides what happens to blanks, which I will come back to. The third argument is the text, and this is where it pulls ahead of the old approach. You hand it an entire range instead of clicking cell after cell.
To join five columns into one labeled string, you would write =TEXTJOIN(“, “, TRUE, A2:E2). That single formula covers all of it. When a range stretches across multiple rows and columns, TEXTJOIN works through each row from left to right, then moves to the next one down.
TEXTJOIN arrived in Excel 2019 and works in 2021, 2024, and Microsoft 365, on Mac as well as Windows. Since I split my time across a few Macs, that coverage matters. Anything before 2019 leaves you without it. Watch for one catch: numbers turn into text on their own, but dates arrive as serial numbers, so wrap any date in the TEXT function to hold its formatting.
The blank-cell handling that saved the most time
The ignore_empty switch does the cleanup for you
That second argument, ignore_empty, is where TEXTJOIN earned its keep for me. Set it to TRUE and the function skips empty cells, so no stray separators ever appear. The gap-hunting that used to eat my afternoons simply stopped being a task. Set it to FALSE and the blanks stay in, delimiters and all, which is occasionally what you want when each position has to line up with a fixed template.
The argument also opens up some combinations worth knowing. Wrap FILTER inside the function, and TEXTJOIN keeps only the matching rows in that single cell. This has gotten more useful now that FILTER refreshes on its own as your data changes. UNIQUE does something similar, clearing duplicates from a range before the join.
Two limits matter here. Everything ends up in one cell, which caps at 32,767 characters; go past that and you get a #VALUE! error. A cell holding only a space also reads as filled rather than empty, so clear those with TRIM before they slip in.
Where TEXTJOIN fits with the rest of my text toolkit
Pairing it with the newer functions Excel keeps adding
I rarely use TEXTJOIN in isolation now. It tends to sit at the end of a short cleanup pipeline as the step that assembles everything into its final shape. Messy source data usually needs work before it is worth joining, and Excel has added several functions that handle that prep well.
The regex functions are my first stop for scrubbing. REGEXEXTRACT can pull a region code or an order number out of a cluttered string, and REGEXREPLACE can strip or mask characters before anything gets combined. TRIMRANGE solves a different problem by cutting the empty rows and columns that pad imported data, so a stray block of blanks never reaches my formula. Excel added all of these fairly recently, and they sit among the functions worth learning this year. Each one feeds cleaner input into a function built to reassemble it.
Once I stopped thinking of it as a neat shortcut and started using it as that final assembly step, TEXTJOIN turned into one of the formulas I use most often.
- OS
-
Windows, macOS
- Supported Desktop Browsers
-
All via web app
Why TEXTJOIN earned a permanent spot in my formulas
The job that once cost me an afternoon of typing and cleanup now fits in a single formula I can copy down an entire column. That is the whole pitch. TEXTJOIN does nothing exotic, and that is exactly why it belongs in regular rotation instead of a list of clever party tricks. If you have been chaining cells together with ampersands out of habit, swap in TEXTJOIN on your next messy combine and let the ignore_empty argument handle the parts you used to fix by hand. Once you start pairing it with dynamic array functions, plain text in a spreadsheet starts to feel a lot more capable.