How to use the TEXTJOIN function
The TEXTJOIN function is a new function for office 365 subscribers, it is like the CONCATENATE function on steroids or what the CONCATENATE function should have been from the beginning.
It allows you to combine text strings from multiple cell ranges and also use delimiting characters if you want. Not only that, you can also use it in array formulas.
Excel Function Syntax
TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
Arguments
delimiter | Required. The delimiting character or characters you want to use. |
ignore_empty | Required. True lets you ignore empty cells in the third argument, False adds empty cells to the output. |
text1 | Required. The cell range you want to concatenate. |
[text2] | Optional. Up to 254 additional cell ranges. |
Comments
The similar CONCATENATE function forced you to select each cell in order to join text strings, see picture below. If you had to combine many many cells this function was not option, you had to use a custom vba function.
You can use the TEXTJOIN function with a cell range or multiple cell ranges, this may save you a lot of time if you are working with many cells.
Example 1
The first argument is the delimiter, the example below shows you the result in cell C4 if you use no delimiting characters. The second argument lets you choose between TRUE or FALSE, if TRUE it will ignore empty cells. The third argument is the cell range.
The following article shows you how to add unique distinct values to a cell using the TEXTJOIN function:
Concatenate unique distinct values
The new TEXTJOIN function in excel 2016 lets you do some amazing things with arrays. This post demonstrates how to […]
Concatenate unique distinct values
Example 2
This picture shows you the TEXTJOIN function combining multiple cell ranges with a delimiting text string. It also ignores the empty cell A5. You can use up to 252 cell ranges.
TEXTJOIN function and array formulas
Example 3 - Concatenate values based on a condition
It is possible to use this function in an array formula, here is a simple demonstration.
You can see that the third argument contains:
This IF function checks if the numbers in cell range A1:A9 are above 5 and if they are the corresponding value in cell range B1:B9 is returned.
Numbers 6,7,8 and 9 are larger than 5 so corresponding characters F, G, H and I are combined using the delimiting characters "--".
This post demonstrates how to do a lookup and concatenate returning values:
Lookup and return multiple values concatenated into one cell
This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]
Lookup and return multiple values concatenated into one cell
TEXTJOIN function vba for previous excel versions
The following user defined function (udf) allows you to combine text strings just like the TEXTJOIN function.
Why would you want to use this udf? TEXTJOIN function is only available in Excel 365, previous excel versions are missing this function.
VBA code
Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, ParamArray cell_ar() As Variant) For Each cellrng In cell_ar For Each cell In cellrng If ignore_empty = False Then result = result & cell & delimiter Else If cell <> "" Then result = result & cell & delimiter End If End If Next cell Next cellrng TEXTJOIN = Left(result, Len(result) - Len(delimiter)) End Function
Where do I put this code?
- Open VB Editor (Alt + F11)
- Click "Insert" on the menu
- Click "Module"
- Copy code above
- Paste it to the code module
- Exit VB Editor
How do I use it?
- Select a cell
- Type Textjoin(
- Type your three arguments and an ending parentheses.
- Press Enter
Arguments
delimiter - A text string, if you want nothing type ""
ignore_empty - If TRUE it ignores empty cells
text1, text2, ... - A cell range or multiple cell ranges. Use a comma to separate them.
Articles with the 'TEXTJOIN' Function
The following 3 articles have formulas that contain the TEXTJOIN function.
Lookup and return multiple values concatenated into one cell
This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]
Joining multiple cell values in Excel is not easy, for example, the CONCATENATE function allows you to only reference a […]
Concatenate unique distinct values
The new TEXTJOIN function in excel 2016 lets you do some amazing things with arrays. This post demonstrates how to […]
Functions in 'Text'
The TEXTJOIN function function is one of many functions in the 'Text' category.
The ASC function converts full-width (double-byte) characters to half-width (single-byte) characters. This function is for double-byte character set (DBCS) languages. […]
Use CHAR function to convert a number to the corresponding character. This is determined by your computers character set. Windows ANSI […]
The CLEAN function deletes nonprintable characters in a value, more specifically, it is designed to delete the first 32 nonprinting […]
The CODE function returns a specific number for the first character of the text argument, determined by your computers character set. […]
How to use the CONCAT function
The CONCAT function concatenates values from multiple cells. There is, however, no delimiting character or a way to ignore empty […]
How to use the CONCATENATE function
The CONCATENATE function lets you add text strings into one single text string. The function has been replaced by the […]
The EXACT function allows you to check if two values are precisely the same, it returns TRUE or FALSE. The […]
The FIND function returns the position of a specific string in another string, reading left to right. Note, the FIND function […]
The FIXED function rounds a number to the specified number of decimals, formats the number in decimal format using a […]
The LEFT function extracts a specific number of characters always starting from the left. Excel Function Syntax LEFT(text, [num_chars]) Arguments […]
The LEN function returns the number of characters in a cell value. Formula in cell D3: =LEN(B3) Cell B7 contains […]
The LOWER function converts a value to lower case letters. Formula in cell C3: =LOWER(B3) Excel Function Syntax LOWER(text) Arguments […]
The MID function returns a substring from a string based on the starting position and the number of characters you want […]
The REPT function repeats a specific text a chosen number of times. Formula in cell D3: =REPT(B3,C3) Excel Function Syntax […]
The RIGHT function extracts a specific number of characters always starting from the right. Excel Function Syntax RIGHT(text,[num_chars]) Arguments text […]
How to use the SEARCH function
Returns a number representing the position a substring is, if found in a textstring. FIND is case sensitive and SEARCH is not.
How to use the SUBSTITUTE function
The SUBSTITUTE function replaces a specific text string in a value. Case sensitive. Formula in cell E3: =SUBSTITUTE(B3,C3,D3) Excel Function […]
The T function returns a text value if the argument is a text value. Formula in cell C3: =T(B3) The […]
Excel function syntax TEXT(value, format_text) Converts a value to text in a specific number format. Arguments value - The string […]
How to use the TEXTJOIN function
The TEXTJOIN function is a new function for office 365 subscribers, it is like the CONCATENATE function on steroids or what the CONCATENATE function […]
The picture above shows you values that have multiple leading and trailing blanks, sometimes also between words. The TRIM function […]
How to use the UNICHAR function
Use the UNICHAR function to calculate a character based on a number. Excel Function Syntax UNICHAR(number) Arguments number Required. The […]
How to use the UNICODE function
Use the UNICODE function to return a Unicode number based on a character. Excel Function Syntax UNICHAR(text) Arguments text Required. […]
Converts a value to upper case letters. Formula in cell C3: =UPPER(B3) Excel Function Syntax UPPER(text) Arguments text Value to convert. Required. Excel […]
Converts a text string that represents a number to a number. This function is included for compatibility with other software. […]
14 Responses to “How to use the TEXTJOIN function”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form
[…] versions to excel 2016, however if you have excel 2016 you can now finally use the much easier TEXTJOIN function. Here is a larger range, A1:A50 with some […]
I followed the instructions but when I type TEXTJOIN( in a cell I get an error "Ambiguous name detected: TEXTJOIN".
I am new to using Visual Basic so am probably doing something silly but it doesn't seem to recognize the TEXTJOIN function I've created
Any ideas?
josh,
This error occurs when there are multiple UDFs with the same name in a module.
Not working for me. Get VALUE! error
lesli
Can you post your formula here?
Also after finding the function, it commits to the SS all lowercase not upper. That is an alert to me as my other functions keep the exact name case format.
I followed the instructions for TEXTJOIN unique values and got the curly brackets, but cell was empty.
It may have something to do with the fact that my array is a list of horizontal cells rather than vertical ?
I tried "column" instead of "row" in the the function but got "#value!"
Please help.
Can you post the formula?
Hi - Thanks for posting this - as I've had to make my spreadsheet backwards compatible with older versions of Excel.
I've been using this UDF for some time but have found that myself and others receive an occasional error where the first line of code (Function TEXTJOIN1(delimiter As String, ignore_empty As Boolean, ParamArray cell_ar() As Variant)) is highlighted in the VB editor with the following error being thrown on Excel opening the file: "Automation error Catastrophic failure". I've done some research and have yet to figure out why this may be throwing an error on some machines intermittently and not others. Some posts point to using 'Range' instead of 'String' ... which I've tried with no luck.
Wasn't sure if anyone else had encountered this?
Thanks!
ASmyth
"Automation error Catastrophic failure"
That is an error I never have encountered or heard of before.
This StackOverflow page suggests a few solutions:
https://stackoverflow.com/questions/31888880/automation-error-catastrophic-failure-excel-vba
Hi, I have used this formula {=TEXTJOIN(",",TRUE,IF(I2=$A$2:$A$4785,$B$2:$B$4785,""))} to link text from different cells which has worked for the most of my data, but for some I get the #Value error appear. I cannot see why I am getting this as the cells are formatted the same and there is nothing seemingly different. Any ideas?
Thanks and thanks for creating the above, its really useful
Is there a way to use the textjoin function with more than 3000 records?
Mike,
The TEXTJOIN function returns a #VALUE! error if the returned value has more than 32767 characters.
https://support.office.com/en-us/article/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c