Forum Discussion
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
Excel allows maximum 32767 characters for the text saved in the cell. It doesn't matter by which formula such text is received.
- ysindhurCopper ContributorThank you !
- NikolinoDEGold Contributor
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:
- Press ALT + F11 to open the VBA editor.
- Go to "Insert" > "Module" to insert a new module.
- Paste the above code into the module window.
- Close the VBA editor.
- 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.