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.v20.2.Core.dll

NuGet Package: DevExpress.Spreadsheet.Core

NuGet Packages: DevExpress.Spreadsheet.Core, DevExpress.WindowsDesktop.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:

Library	Object Type	Path to TruncateLongStringsInFormulas
WinForms Controls	SpreadsheetControlOptions	.Compatibility .TruncateLongStringsInFormulas
Office File API	DocumentOptions	.Compatibility .TruncateLongStringsInFormulas
	WorkbookOptions	.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:

View Example: Use the CONCATENATE Function to Update Formulas with Text Values Longer Than 255 Characters

Related GitHub Examples

The following code snippet (auto-collected from DevExpress Examples) contains a reference to the TruncateLongStringsInFormulas property.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

C#

spreadsheet-use-concatenate-function-to-update-long-strings-in-formulas/CS/SpreadsheetApp/Form1.cs#L12

InitializeComponent();
spreadsheetControl1.Options.Compatibility.TruncateLongStringsInFormulas = false;
spreadsheetControl1.LoadDocument("FileWithLongStringInFormula.xlsx");

VB.NET

spreadsheet-use-concatenate-function-to-update-long-strings-in-formulas/VB/SpreadsheetApp/Form1.vb#L11

InitializeComponent()
spreadsheetControl1.Options.Compatibility.TruncateLongStringsInFormulas = False
spreadsheetControl1.LoadDocument("FileWithLongStringInFormula.xlsx")