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.v24.1.Core.dll

NuGet Package: DevExpress.Spreadsheet.Core

Declaration

C#

[DefaultValue(true)]
public bool TruncateLongStringsInFormulas { get; set; }

VB.NET

<DefaultValue(True)>
Public Property TruncateLongStringsInFormulas As Boolean

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	.Compatibility .TruncateLongStringsInFormulas

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:

C#

spreadsheetControl1.Options.Compatibility.TruncateLongStringsInFormulas = false;

VB.NET

spreadsheetControl1.Options.Compatibility.TruncateLongStringsInFormulas = False

Use the code below to turn off this option for a non-visual Workbook instance.

C#

using(var workbook = new Workbook())
{
    workbook.Options.Compatibility.TruncateLongStringsInFormulas = false;
}

VB.NET

Using workbook As New Workbook()
    workbook.Options.Compatibility.TruncateLongStringsInFormulas = False
End Using

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: