TEXTJOIN function is showing calc error

Copper Contributor

Hi 

I have text to join using delimiter which is 10000 rows. I have used TEXTJOIN function, but it is not combining all the data that is required for a data retrieval through Oracle. Is there any other function which combines large sets of text with delimiter. Please advise

3 Replies

@ysindhur 

Excel allows maximum 32767 characters for the text saved in the cell. It doesn't matter by which formula such text is received.

@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.

Thank you !