Forum Discussion

ysindhur's avatar
ysindhur
Copper Contributor
Apr 19, 2024

TEXTJOIN function is showing calc error

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

  • ysindhur 

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

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    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.

Resources