How to Fix TEXTJOIN Calculation Error and Excel Text Length Limit
Question details
Users experience a calculation error when using the TEXTJOIN function to combine thousands of rows, caused by the application's strict character limitations.

- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Attempting to concatenate a very large dataset or thousands of rows into one single cell using the TEXTJOIN formula.
- Observed behavior
- The formula returns a calculation error because the concatenated text exceeds the maximum supported limit of 32,767 characters per cell.
Check the estimated total character count of the data you are trying to combine. If it exceeds 32,767 characters, no formula can force it into a single cell, and you will need to split the data or export it.
Use a VBA User-Defined Function to Concatenate Cells
Implement a custom VBA macro to cleanly combine non-empty cells and provide a foundation for exporting data if it exceeds cell limits.
While a standard spreadsheet cell is strictly limited to 32,767 characters, you can use a VBA User-Defined Function (UDF) to process large datasets more intelligently. This method efficiently skips empty cells.
Keep in mind that the 32,767 character limit still applies to the final cell output. For results larger than this, you can modify the VBA script to output directly to a text file rather than a cell.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
Click on 'Insert' in the top menu bar, then select 'Module' to create a blank script window.
Copy and paste the following code into the module window: Function ConcatenateTextsWithDelimiter(rng As Range, Optional delimiter As String = ",") As String Dim cell As Range, result As String For Each cell In rng If Len(cell.Value) > 0 Then result = result & cell.Value & delimiter Next cell If Len(result) > 0 Then result = Left(result, Len(result) - Len(delimiter)) ConcatenateTextsWithDelimiter = result End Function
Return to your worksheet, click on the target cell, and enter the formula: =ConcatenateTextsWithDelimiter(A1:A10000, ",") to combine the data.
If your combined string is longer than 32,767 characters, the formula will still fail to output in one cell. In this case, adjust your range (e.g., A1:A5000 and A5001:A10000) to split the results across multiple cells, or use advanced VBA to write the 'result' variable directly to an external .txt file.

Handle Large Datasets and VBA Effortlessly with WPS Spreadsheet
WPS Spreadsheet natively supports advanced functions like TEXTJOIN and custom VBA macros, empowering you to process extensive text data effectively.
- 1. Install WPS Office: Download and install WPS Office, then launch WPS Spreadsheet and open your existing workbook.
- 2. Utilize Built-in Formulas: Use the standard TEXTJOIN function from the Formulas tab to combine smaller, manageable ranges of data.
- 3. Access the Developer Tools: For massive datasets, navigate to the Developer tab to open the VBA editor and insert the custom macro provided in the solution.
- 4. Process the Data: Run the custom formula to cleanly concatenate your non-empty cells while managing character limits.

Frequently Asked Questions
What is the maximum character limit for a single spreadsheet cell?
Spreadsheet applications, including Microsoft Excel and WPS Spreadsheet, allow a maximum of 32,767 characters in a single cell. This limit applies regardless of whether the text is manually typed or generated by a formula.
Why does my TEXTJOIN formula suddenly return a calculation error?
A calculation error (such as #VALUE!) often occurs with TEXTJOIN when the resulting concatenated string exceeds the 32,767 character limit. Trying to join thousands of rows of text will quickly hit this hard boundary.
Can I bypass the 32,767 character limit using a VBA macro?
No, the cell limit is absolute. While VBA can process and store strings much longer than this limit in the background memory, it cannot output a string larger than 32,767 characters into a single cell. You must output the larger strings to an external text file or split them across multiple cells.




