Key Differences: CONCAT, CONCATENATE, and TEXTJOIN
1. CONCAT and CONCATENATE
CONCATENATE
An older Excel function.
Joins two or more text strings together into one string.
Requires each cell or text value to be listed separately in the formula.
Does not allow you to specify a delimiter (separator) between items.
Example: =CONCATENATE(A2, " ", B2) makes "John Doe".
CONCAT
A newer version that replaces CONCATENATE in recent Excel versions.
Joins two or more text strings, can accept ranges as arguments.
Also does not let you specify a delimiter directly.
Example: =CONCAT(A2, " ", B2) or =CONCAT(A2:B2).
2. TEXTJOIN
TEXTJOIN
Available in newer Excel versions.
Combines text from multiple cells or ranges, and lets you specify a delimiter (such as
comma, space).
You can choose whether to ignore empty cells.
More flexible for large ranges or lists.
Example: =TEXTJOIN(" ", TRUE, A2, B2) also returns "John Doe". [1] [2] [3]
Summary Table
Function Accepts Ranges Specify Delimiter Ignore Empty Cells Deprecated/Modern
CONCATENATE No No No Deprecated
CONCAT Yes No No Modern
TEXTJOIN Yes Yes Yes Modern
When to Use Each
CONCATENATE: Older spreadsheets, simple text joining.
CONCAT: General use, joining multiple cells in bulk with no need for delimiter.
TEXTJOIN: When you need a separator and/or to ignore empty cells for cleaner results.
Microsoft recommends using CONCAT and TEXTJOIN for new workbooks, as CONCATENATE
may be phased out in future updates. [2] [3] [1]
If you need differences in SQL or other contexts, let me know!
⁂
1. https://www.linkedin.com/pulse/what-differences-between-concatenate-textjoin-ca-amit-jain-unqzc
2. https://exceljet.net/articles/concat-textjoin
3. https://learn.microsoft.com/en-us/answers/questions/5065568/what-are-the-differences-among-conca
t-contenate-te
4. https://community.qlik.com/t5/QlikView-App-Dev/What-is-the-different-between-concat-and-concaten
ate/td-p/1583054
5. https://stackoverflow.com/questions/73100995/what-is-the-difference-between-concat-and-concatena
te
6. https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-concatenate
7. https://iatric.com/tsql-concatenation-operator-vs-concat-vs-concat-ws/
8. https://solvepro.co/concat-vs-concat-ws/
9. https://www.stratascratch.com/blog/concat-in-sql-tips-and-techniques-for-efficient-queries/
10. https://forums.oracle.com/ords/apexds/post/difference-between-concat-and-concatenation-operator-
7894
