SpreadsheetCompatibilityOptions.TruncateLongStringsInFormulas Property
Specifies whether the Spreadsheet should truncate string constants in formulas to 255 characters when it opens documents with formulas where this limit is exceeded.
Namespace: DevExpress.XtraSpreadsheet
Assembly: DevExpress.Spreadsheet.v23.2.Core.dll
NuGet Package: DevExpress.Spreadsheet.Core
Declaration
Property Value
| Type | Default | Description |
|---|---|---|
| Boolean | true | true to truncate string constants in formulas to 255 characters; otherwise, false. |
Property Paths
You can access this nested property as listed below:
| Object Type | Path to TruncateLongStringsInFormulas |
|---|---|
| DocumentOptions |
|
Remarks
Microsoft® Excel® does not allow users to create formulas that contain string constants longer than 255 characters. It displays the following warning if this limit is exceeded:
The WinForms Spreadsheet control and Spreadsheet Document API also support this limitation. The Spreadsheet control replicates Microsoft® Excel® behavior and displays the error message shown below when users create or modify formulas with string constants longer than 255 characters. Users can click OK to correct the formula.
If you programmatically create formulas that contain string constants longer than 255 characters, the Spreadsheet throws ArgumentException.
The TruncateLongStringsInFormulas option specifies how to process existing documents that contain incorrect formulas with text strings longer than 255 characters. These documents could be generated in early versions of the Spreadsheet components (see this bug report for details: T801223). The default value of the TruncateLongStringsInFormulas option is true, and the Spreadsheet truncates long strings in formulas to 255 characters when it opens a document. In this case, affected formulas can return incorrect results. To avoid this, disable the TruncateLongStringsInFormulas property to prevent formula truncation and update long strings (programmatically or from the user interface) to match the character limit. If you do not fix incorrect formulas and resave the document or export it to another Excel format, the Spreadsheet truncates string constants in formulas to 255 characters to produce a valid file.
The following code snippet demonstrates how to disable the TruncateLongStringsInFormulas option for the WinForms Spreadsheet control:
Use the code below to turn off this option for a non-visual Workbook instance.
using(var workbook = new Workbook())
{
workbook.Options.Compatibility.TruncateLongStringsInFormulas = false;
}
We recommend that you use the CONCATENATE function or the concatenation operator (&) to create formulas with string constants longer than 255 characters. The following GitHub example demonstrates how to iterate through all formulas in a workbook and split long strings in formulas with the CONCATENATE function: