If you've ever spent ten minutes manually combining cells or cutting text apart in Excel, you'll appreciate what TEXTJOIN, TEXTBEFORE, and TEXTAFTER can do for you. These three Excel formulas put delimiters front and center, letting you join, split, and extract text with far less effort than the old methods.
What Is a Delimiter in This Context?
A delimiter is just a character (or string of characters) that marks a boundary between pieces of data. In Excel formulas, you specify a delimiter to tell the function where to cut or where to join. Common examples include commas, spaces, pipes, semicolons, and hyphens.
Once you understand that idea, these three functions become much easier to reason about.
TEXTJOIN: Combine Text with a Delimiter
TEXTJOIN merges multiple text values into one string, placing your chosen delimiter between each item. Its syntax is straightforward:
- Pick your delimiter (for example, a comma and space).
- Set ignore_empty to TRUE or FALSE to skip blank cells or include them.
- Choose the range or individual cells you want to combine.
A basic example: =TEXTJOIN(", ", TRUE, A2:A6) joins all values in A2 through A6 with a comma and space between them. If A4 is blank and you've set ignore_empty to TRUE, it won't leave a double comma in the output.
Tip: TEXTJOIN replaced the old CONCATENATE workarounds. If you're still manually stringing cells together with & operators, switch to TEXTJOIN and save yourself the frustration.
TEXTBEFORE: Extract Text Before a Delimiter
TEXTBEFORE grabs everything to the left of a specified delimiter. It's perfect when you have structured text, like full names or file paths, and you only want the first segment.
Syntax: =TEXTBEFORE(text, delimiter, [instance_num]). The optional instance_num argument lets you target a specific occurrence of the delimiter, which is useful when there are multiple separators in one cell.
For example, if A1 contains "London, UK, Europe", then =TEXTBEFORE(A1, ",") returns "London". Add instance_num as 2 and you get "London, UK" instead.
TEXTAFTER: Extract Text After a Delimiter
TEXTAFTER works the same way but pulls everything to the right of the delimiter. Using the same example, =TEXTAFTER(A1, ", ") returns "UK, Europe". It's the natural complement to TEXTBEFORE.
You can also use negative instance numbers in both functions to count from the end of the string rather than the start. That's a handy trick when you want the last segment of a path or ID.
Quick Comparison
| Function | What It Does | Example Result |
|---|---|---|
| TEXTJOIN | Joins multiple values with a delimiter | "cat, dog, fish" |
| TEXTBEFORE | Returns text before a delimiter | "London" from "London, UK" |
| TEXTAFTER | Returns text after a delimiter | "UK" from "London, UK" |
A Practical Workflow Example
Say you have a column of email addresses and you need to separate the username from the domain. TEXTBEFORE and TEXTAFTER make this a one-formula job. =TEXTBEFORE(A2, "@") gives you the username. =TEXTAFTER(A2, "@") gives you the domain.
Then, if you want to rebuild a cleaned-up list of domains into a single cell, TEXTJOIN handles that. These functions work naturally together as part of the same data-cleaning process.
Warning: TEXTBEFORE and TEXTAFTER are only available in Excel 365 and Excel 2021 or later. If you're on an older version, you'll need to use LEFT, RIGHT, MID, and FIND as substitutes.
Working with Delimiters Outside Excel
Sometimes your data starts life outside Excel, as a CSV export or a raw text file with inconsistent delimiters. Before you even open Excel, you might need to reformat those separators. The online delimiter converter at Delimiter.site lets you swap one delimiter for another in seconds, without writing a single formula.
Once your file uses a consistent delimiter, Excel formulas like the ones above become much more reliable. Garbage in, garbage out still applies.
Key Points
- TEXTJOIN combines a range of cells using any delimiter you choose, and it handles blank cells cleanly.
- TEXTBEFORE extracts everything to the left of a delimiter, with support for targeting specific occurrences.
- TEXTAFTER does the same from the right side, and both support negative instance numbers for counting from the end.
- All three functions are available in Excel 365 and Excel 2021+, but not in older versions.
- If your source data has messy or mixed delimiters, clean it up first with a tool like the comma to pipe converter before pulling it into Excel.
Put These Formulas to Work
TEXTJOIN, TEXTBEFORE, and TEXTAFTER are genuinely useful once you get comfortable with the delimiter argument. They cover the most common text-splitting and text-joining tasks without any complex helper columns or VBA.
Start with a small dataset, try each formula once, and you'll quickly see where they fit into your regular workflow. And if you need to convert delimiters online before importing data into Excel, that step is just as quick.