TEXTJOIN function is showing calc error

ysindhur 

If you are encountering calculation errors with the TEXTJOIN function due to the large dataset, you might be hitting Excel's limitations on function parameters or cell contents. One alternative approach is to use a VBA macro to concatenate the text.

Here is a sample VBA code that you can use:

Vba code is untested, please backup your file.

Function ConcatenateTextsWithDelimiter(rng As Range, Optional delimiter As String = ",") As String
    Dim cell As Range
    Dim result As String
    
    For Each cell In rng
        If cell.Value <> "" Then
            result = result & cell.Value & delimiter
        End If
    Next cell
    
    ' Remove the last delimiter
    If Len(result) > 0 Then
        result = Left(result, Len(result) - Len(delimiter))
    End If
    
    ConcatenateTextsWithDelimiter = result
End Function

Function ConcatenateTextsWithDelimiter(rng As Range, Optional delimiter As String = ",") As String Dim cell As Range Dim result As String For Each cell In rng If cell.Value <> "" Then result = result & cell.Value & delimiter End If Next cell ' Remove the last delimiter If Len(result) > 0 Then result = Left(result, Len(result) - Len(delimiter)) End If ConcatenateTextsWithDelimiter = result End Function

To use this function:

1. Press ALT + F11 to open the VBA editor.
2. Go to "Insert" > "Module" to insert a new module.
3. Paste the above code into the module window.
4. Close the VBA editor.
5. You can then use this custom function ConcatenateTextsWithDelimiter in your Excel worksheet like any other function. For example, you can use it like this: =ConcatenateTextsWithDelimiter(A1:A10000, ",")

This function iterates through the specified range (rng) and concatenates non-empty cell values with the specified delimiter. It should handle larger datasets more effectively than the TEXTJOIN function in Excel. The text, steps and code were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.