Formula Errors

How to Fix TEXTJOIN Calculation Error and Excel Text Length Limit

Huma Ashraf ChHuma Ashraf Ch Nov 11, 2026 869 views

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.

How to Fix TEXTJOIN Calculation Error and Excel Text Length Limit
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

2
Insert a New Module

Click on 'Insert' in the top menu bar, then select 'Module' to create a blank script window.

3
Paste the Custom VBA Code

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

4
Apply the Formula in Your Spreadsheet

Return to your worksheet, click on the target cell, and enter the formula: =ConcatenateTextsWithDelimiter(A1:A10000, ",") to combine the data.

5
Split Data or Export to Text File

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.

Use a VBA User-Defined Function to Concatenate Cells
Hard Limit Warning: The 32,767 character limit is a hard application limit. Even with custom VBA functions, a single cell cannot display or store text exceeding this amount.
Advanced Data Management

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. 1. Install WPS Office: Download and install WPS Office, then launch WPS Spreadsheet and open your existing workbook.
  2. 2. Utilize Built-in Formulas: Use the standard TEXTJOIN function from the Formulas tab to combine smaller, manageable ranges of data.
  3. 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. 4. Process the Data: Run the custom formula to cleanly concatenate your non-empty cells while managing character limits.
Full compatibility with Microsoft Excel formulas including TEXTJOINBuilt-in VBA and Macro support for deploying User-Defined Functions easilyLightweight architecture that handles large datasets and rows smoothlyFree to download with a highly familiar, easy-to-navigate user interface
QA img-9

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.