Formula Errors

How to Fix the Excel #CALC! Error: Text Too Long in CONCAT and XLOOKUP

Emma Brown Oct 19, 2026 869 views

Question details

The user is encountering a #CALC! error when trying to combine data using CONCAT, CONCATENATE, and XLOOKUP because the resulting text string is too long.

How to Fix the Excel #CALC! Error Caused by Text That Is Too Long

Product: Microsoft Excel
Device & OS: not provided
Scenario: Combining multiple text values into a single cell using CONCAT and XLOOKUP functions.
Observed behavior: The formula returns a #CALC! error because the combined text exceeds the maximum character limit for a cell. Adjusting Data Validation settings does not resolve the calculation issue.

Before you start

Before troubleshooting, keep in mind that a single cell in a spreadsheet has a hard limit of 32,767 characters. Verify if the expected combined text from your arrays exceeds this limitation.

Solution 1Recommended

Diagnose and Shorten the Formula Output Length

Identify which part of your XLOOKUP and CONCAT functions is causing the text string to exceed cell limits, and reduce the output.

The #CALC! error typically occurs in array calculations or when a function attempts to produce a result that exceeds the application's capabilities. A single cell can only contain up to 32,767 characters.

When retrieving massive text strings from multiple cells and combining them with CONCAT or CONCATENATE, it is very easy to breach this limit. Note that changing Data Validation settings (like Custom or Text Length rules) will not fix this, as those settings only restrict manual user input and do not change formula calculation limits.

Isolate the XLOOKUP results

Temporarily separate your XLOOKUP formulas into different cells in a blank row or column to see what data each one returns individually.

Check character counts

Use the =LEN(A1) function (replacing A1 with your individual XLOOKUP output cells) to calculate the exact text length of each retrieved segment.

Reduce the combined array

If the total sum of your lengths exceeds 32,767, you must split your final CONCAT formula to output across multiple cells, or truncate the source data.

Diagnose and Shorten the Formula Output Length

Data Validation Cannot Fix Calculation Limits: Data validation is only used to control what a user can manually type into a cell. It cannot override or manipulate formula output bounds.

Solution 2

Debug Using a Dummy Data Sample Workbook

Create a simplified version of your workbook with dummy data to isolate the specific XLOOKUP or CONCAT component causing the calculation error.

Free Microsoft Office alternative

Try WPS Office for Seamless Spreadsheet Management

If strict software limitations and complex formula errors are slowing down your data processing workflow, consider switching to WPS Office. WPS Spreadsheet is a free, lightweight alternative offering robust compatibility, familiar interfaces, and seamless migration for your daily analytical tasks.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Supports advanced functions including XLOOKUP, CONCAT, and dynamic arrays to handle complex formulas.Lightweight application that runs smoothly on Windows, Mac, and Linux systems.Completely free to download with a highly familiar user interface, requiring zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

What does the #CALC! error mean in Excel formulas?

The #CALC! error occurs when the spreadsheet software encounters an array calculation or function result it cannot process. Common causes include empty arrays, unsupported nested arrays, or text outputs that exceed the maximum character limit for a single cell.

What is the maximum text length a single spreadsheet cell can hold?

A single cell in standard spreadsheet software like Microsoft Excel can hold a maximum of 32,767 characters. If a formula like CONCAT attempts to output a combined string longer than this, it will return a #CALC! error.

Can Data Validation settings fix the #CALC! error?

No. Data Validation settings strictly restrict manual data entry (what a user types directly). They do not affect, expand, or resolve formula calculation limits such as excessive text lengths generated by combining CONCAT and XLOOKUP.

Is there a workaround for concatenating massive amounts of data?

Instead of concatenating all data into a single cell, consider splitting the final output across multiple adjacent columns. For extremely large datasets, using tools like Power Query to merge and transform data is much more efficient and avoids hitting cell-level character limits.

Excel & Data Problems