How to Use the TEXTJOIN Excel Function? 3 Easy Examples
(Note: The TEXTJOIN Excel function is suitable for Excel 2019 and Office 365 versions)
TEXTJOIN Excel Function: A Summary
The TEXTJOIN Excel function, as the name suggests, joins two or more text strings into a single output string. It is not that different from the CONCAT function, except TEXTJOIN concatenates by default by including delimiters.
Its main purpose is to join two or more text values with a specific delimiter. It will return the concatenated text string, along with the specified delimiter as the output.
In this short guide, you’ll learn:
Related:
The FORMULATEXT Excel Function – 2 Best Examples
The Excel CHOOSE Function – 4 Best Uses
How to Autofit Excel Cells? 3 Best Methods
TEXTJOIN Syntax and Arguments
The TEXTJOIN formula has the following syntax:
TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...text_n])A delimiter is any string or character that separates each text value of the concatenated string. Usually, this will be a comma, a space, or a hyphen. But, in some cases, it can even be as long as a phrase. For example, space is the delimiter in the name string “Microsoft Office”. Hyphen is the delimiter in the string “Alpha-Bravo-Delta”.
Please keep in mind that you have to insert the delimiter within double-quotes. For example, to join text in cells A2 and B2 with a comma, use TEXTJOIN(“,”,TRUE,A2,B2). To use no delimiter, add a double-quote with no space i.e “”.
Set ignore_empty to TRUE if you want to ignore empty cells while joining text. If you want to include empty cells in the concatenations, set it to FALSE. Instead of using the BOOLEAN TRUE or FALSE, you can also use the binary equivalent ‘1’ or ‘0’.
text1, text2, ….. text_n is the list of all the cells that contain the text strings you want to join. The maximum number of strings that can be joined using TEXTJOIN is 252.
Also Read:
How to Unmerge Cells in Excel? 3 Best Methods
How to Add Subscript in Excel? (6 Best Methods)
How to Apply the Accounting Number Format in Excel? (3 Best methods)
TEXTJOIN Excel Examples
Now, let us see the Excel TEXTJOIN function in action with the help of some interesting examples.
Use TEXTJOIN to Combine Names
You can use the formula =TEXTJOIN(“ “,1,A2,B2) to join the names in cells A2 and B2 together with a space in between them. Notice that here I have used a binary input for ignore_empty.
The formula=TEXTJOIN(” ,”,1,A3,B3) will join the names in cells E2 and B2 together with a comma in between them.
Use TEXTJOIN to Concatenate a Range
Use the formula, =TEXTJOIN(“ – “,TRUE,E1:G1), to concatenate the entire range (E1 to G1) in one go.
You can also concatenate individual cells and ranges inside the TEXTJOIN function. For example, the formula, =TEXTJOIN(“-“,TRUE,E1:G1,I1), will concatenate each value inside the range E1 to G1 and finally join it with I1.
How to Keep Source Formatting in TEXTJOIN?
If you are using the TEXTJOIN function to concatenate dates or other special formats, Excel will either display a #VALUE error or strip the special formatting before it joins them together.
To avoid this, convert these special formats to text strings using the TEXT function, like this, TEXT( B2, “mm/dd/yy”), before using them inside the TEXTJOIN formula.
Things to Keep in Mind about TEXTJOIN
- The CONCAT function is very similar to the TEXTJOIN function, except that the delimiters have to be entered every time in between two strings.
- A simpler method than CONCAT is to use the “&” operator, also known as the ampersand method.
- TEXTJOIN works only in Excel 2019 and Office 365 versions of Excel.
Suggested Reads:
How to Enable Excel Dark Mode? 3 Simple Steps
How to Delete a Pivot Table in Excel? 4 Best Methods
How to Make Excel Track Changes in a Workbook? 4 Easy Tips
Closing Thoughts
In this guide, I have shown how to properly use the Excel TEXTJOIN function. Do try these methods in a rough spreadsheet to gain a better understanding. If you have any questions about this or any other Excel feature, feel free to let us know in the comments. We’re always happy to help.
Visit our free Excel Resources Centre for more high-quality Excel guides.
Ready to dive deep into Excel? Click here for advanced Excel courses with in-depth training modules.
Simon Sez IT has been teaching Excel and other business software for over ten years. For a low, monthly fee you can get access to 100+ IT training courses.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
