Excel CONCAT and TEXTJOIN Functions
last modified October 5, 2026
The CONCAT and TEXTJOIN functions are powerful text
manipulation tools in Excel. They combine text from multiple cells or strings
with various options. This tutorial provides a comprehensive guide to using
these functions. You'll learn their syntax, differences, and practical
applications to master text concatenation in Excel.
Function Basics
CONCAT and TEXTJOIN combine text strings but with
different capabilities. CONCAT simply joins items while TEXTJOIN offers more
control. Both are essential for text manipulation tasks in Excel.
| Function | Description | Syntax |
|---|---|---|
| CONCAT | Joins text items without delimiters | =CONCAT(text1, [text2], ...) |
| TEXTJOIN | Joins text with specified delimiter | =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...) |
This table compares the two functions. CONCAT is simpler while TEXTJOIN offers
delimiter control and empty cell handling. Both accept individual items and
ranges of cells. CONCAT accepts up to 253 text arguments, TEXTJOIN up to 252.
Either function returns the #VALUE! error if the joined result
exceeds 32,767 characters, the maximum length of a cell.
CONCAT vs CONCATENATE vs TEXTJOIN vs &
Excel offers four ways to join text. Knowing which one to reach for avoids surprises later, especially when ranges or empty cells are involved.
| Method | Delimiter | Range support | Empty cells | Availability |
|---|---|---|---|---|
CONCAT |
No, add manually | Yes | Skipped | Excel 2016+ |
CONCATENATE |
No, add manually | No | Skipped | All versions |
TEXTJOIN |
Yes | Yes | Configurable | Excel 2019+ |
& operator |
No, add manually | No | Skipped | All versions |
CONCATENATE still works but is kept only for compatibility. It
cannot take a range as a single argument, so joining A1:A10 means listing every
cell. CONCAT replaces it and accepts ranges. The &
operator behaves like CONCAT for single items and is the most
portable choice. TEXTJOIN is the only option that inserts a
delimiter automatically and can skip blanks.
Basic CONCAT Example
This example demonstrates the simplest use of CONCAT to join text strings.
=CONCAT("Hello", " ", "World")
This formula combines three text strings: "Hello", a space (" "), and "World". The result is "Hello World". CONCAT joins them in order without any separators unless explicitly included.
CONCAT with Cell References
CONCAT is commonly used to combine text from different cells. Here's an example with first and last names in separate cells.
| A | B | C |
|---|---|---|
| John | Doe | |
| =CONCAT(A1, " ", B1) |
The table shows first name in A1 and last name in B1. The CONCAT formula in C2 combines them with a space separator to create a full name.
=CONCAT(A1, " ", B1)
This formula joins A1 ("John"), a space (" "), and B1 ("Doe"). The result is "John Doe". Note the explicit space needed between names when using CONCAT.
CONCAT with a Range
Unlike CONCATENATE, CONCAT accepts a whole range.
When the range is two-dimensional, values are joined in row-major order: left
to right across each row, then down to the next row.
| A | B | C |
|---|---|---|
| a1 | b1 | =CONCAT(A1:B2) |
| a2 | b2 |
The table holds a 2x2 block in A1:B2. The formula reads across the first row (A1 then B1) and then the second row (A2 then B2).
=CONCAT(A1:B2)
This formula returns "a1b1a2b2". There are no separators, so the values run
together. Use TEXTJOIN when you need a delimiter between them.
TEXTJOIN with Delimiter
TEXTJOIN simplifies concatenation when you need consistent separators. This example joins address components with commas.
| A | B | C | D |
|---|---|---|---|
| 123 | Main St | Springfield | |
| =TEXTJOIN(", ", TRUE, A1:C1) |
The table contains address parts in A1:C1. The TEXTJOIN formula combines them with comma separators, creating a properly formatted address string.
=TEXTJOIN(", ", TRUE, A1:C1)
This formula joins the range A1:C1 using ", " as delimiter. The TRUE parameter ignores empty cells. The result is "123, Main St, Springfield". TEXTJOIN automatically inserts the delimiter between items.
TEXTJOIN Ignoring Empty Cells
TEXTJOIN's ability to skip empty cells is powerful for dynamic data. This example demonstrates this feature.
| A | B | C | D |
|---|---|---|---|
| Red | Blue | ||
| =TEXTJOIN("-", TRUE, A1:C1) |
The table shows color values with an empty cell in B1. The TEXTJOIN formula skips the empty cell when creating the joined string with "-" separators.
=TEXTJOIN("-", TRUE, A1:C1)
This formula joins A1 ("Red") and C1 ("Blue") with "-", skipping empty B1. The result is "Red-Blue". The TRUE parameter ensures empty cells don't create extra delimiters.
TEXTJOIN with ignore_empty FALSE
Setting ignore_empty to FALSE keeps empty cells in the output, so
each one produces an extra delimiter and creates consecutive separators.
| A | B | C | D |
|---|---|---|---|
| Red | Blue | ||
| =TEXTJOIN("-", FALSE, A1:C1) |
The table uses the same data as the previous example, with an empty cell in B1.
=TEXTJOIN("-", FALSE, A1:C1)
This formula returns "Red--Blue". The empty B1 now contributes an empty string, so two delimiters appear in a row. This behavior is useful when the position of each value matters, such as fixed columns exported to a text file.
TEXTJOIN with Line Breaks
TEXTJOIN can create multi-line text by using CHAR(10) as delimiter. This example combines text with line breaks.
| A | B | C |
|---|---|---|
| First Item | Second Item | |
| =TEXTJOIN(CHAR(10), TRUE, A1:B1) |
The table contains two items in A1:B1. The TEXTJOIN formula combines them with line breaks (CHAR(10)) between items. Wrap text must be enabled to display properly.
=TEXTJOIN(CHAR(10), TRUE, A1:B1)
This formula joins A1 and B1 with line breaks. The result appears as two lines in one cell when wrap text is enabled. CHAR(10) is the line-feed character used for line breaks in Windows and modern Excel; older Mac versions used CHAR(13).
Concatenating Numbers and Dates
Numbers, logical values, and dates are converted to text when they are joined.
Numbers and TRUE/FALSE usually look fine, but dates
and times are stored as serial numbers, so concatenation reveals the raw number
unless you format it first.
| A | B | C |
|---|---|---|
| 2026-10-05 (date) | =CONCAT("Date: ", A1) | Date: 46300 |
| 1234.5 (number) | =CONCAT("Total: ", A2) | Total: 1234.5 |
| 2026-10-05 (date) | =CONCAT("Date: ", TEXT(A3, "yyyy-mm-dd")) | Date: 2026-10-05 |
Wrap dates, times, and formatted numbers in TEXT to control the
output. Without it, a date such as October 5, 2026 becomes its serial number,
and a currency value loses its symbol and decimal formatting.
=CONCAT("Date: ", TEXT(A1, "yyyy-mm-dd"))
=CONCAT("Total: ", TEXT(A2, "$#,##0.00"))
The first formula returns "Date: 2026-10-05" and the second returns "Total: $1,234.50", regardless of how the source cells are formatted.
CONCAT vs TEXTJOIN Comparison
While both functions combine text, they serve different purposes. CONCAT is simpler while TEXTJOIN offers more control over the joining process.
| Feature | CONCAT | TEXTJOIN |
|---|---|---|
| Delimiter control | No | Yes |
| Empty cell handling | Skips empty cells | Configurable via ignore_empty |
| Range support | Yes | Yes |
| Excel version | 2016+ | 2019+ |
This comparison table highlights key differences. TEXTJOIN is more flexible but requires newer Excel versions. CONCAT is available in slightly older versions.
CONCAT and TEXTJOIN Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| Joined result exceeds 32,767 characters | #VALUE! error | Shorten the input or split the output across cells |
| More than 253 arguments in CONCAT | Formula is rejected | Combine whole ranges instead of listing every cell |
| More than 252 arguments in TEXTJOIN | Formula is rejected | Pass whole ranges rather than individual cells |
| TEXTJOIN used in Excel 2016 or earlier | #NAME? error | Upgrade to Excel 2019+ or use CONCAT |
| Date shows as a serial number | Five-digit number instead of a date | Wrap the date in TEXT with a format code |
| Unwanted consecutive delimiters | "Red--Blue" | Set ignore_empty to TRUE |
| Delimiter missing between values | Values run together | Add a delimiter, or switch to TEXTJOIN |
| Line breaks not visible | Text appears on one line | Turn on Wrap Text in the cell |
Most problems come from a few recurring causes: an argument count or length
limit, a missing delimiter, a date or number that was never converted with
TEXT, or an empty cell that was not skipped. Checking these first
solves the large majority of concatenation issues.
The CONCAT and TEXTJOIN functions are essential for
text manipulation in Excel. CONCAT provides simple joining of
strings and ranges, while TEXTJOIN adds delimiter control and
optional empty-cell handling. Remember that CONCATENATE and the
& operator remain valid for single items, that numbers and
dates are converted to text before joining, and that the 32,767-character cell
limit always applies. Mastering all four approaches will significantly improve
your text processing capabilities in spreadsheets.
Author
List all Excel Formulas.