SpreadsheetCompatibilityOptions.TruncateLongStringsInFormulas Property

Specifies whether the Spreadsheet control should truncate string constants in formulas to 255 characters when it opens documents with formulas where this limit is exceeded. This is a dependency property.

Namespace: DevExpress.Xpf.Spreadsheet

Assembly: DevExpress.Xpf.Spreadsheet.v20.2.dll

NuGet Packages: DevExpress.WindowsDesktop.Wpf.Spreadsheet, DevExpress.Wpf.Spreadsheet

Declaration

C#

public bool TruncateLongStringsInFormulas { get; set; }

VB.NET

Public Property TruncateLongStringsInFormulas As Boolean

Property Value

Type	Description
Boolean	true to truncate string constants in formulas to 255 characters; otherwise, false. The default value is true.

Property Paths

You can access this nested property as listed below:

Object Type	Path to TruncateLongStringsInFormulas
SpreadsheetControlOptions	.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 WPF 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 control 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 WPF Spreadsheet control:

XAML

<dxsps:SpreadsheetControl.Options>
    <dxsps:SpreadsheetControlOptions>
        <dxsps:SpreadsheetControlOptions.Compatibility>
            <dxsps:SpreadsheetCompatibilityOptions TruncateLongStringsInFormulas="False" />
        </dxsps:SpreadsheetControlOptions.Compatibility>
    </dxsps:SpreadsheetControlOptions>
</dxsps:SpreadsheetControl.Options>

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