Trim reference operator (original) (raw)

Trim reference operator in EPPlus

The trim ref operator can be used in Excel to trim empty rows and columns in a given range. It is available from EPPlus version 8.6 and up. In the Excel UI, the trim ref operator is used directly in the formula, but when inspecting the XML, these operators actually reference functions. Behind the scenes, Excel serializes them in OOXML as internal functions prefixed with _xlfn._TRO_.

There are three ways to use the operator in Excel.

Trim trailing rows and columns

In excel, the operator can be used like this to trim the trailing cells in a range reference:=A1:.E5

To be able to use the operator like in the example above in EPPlus, the _TRO_TRAILING function have to be used to achieve the same result.

sheet.Cells["F1"].Formula = "_TRO_TRAILING(A1:E5)";

Trim leading rows and columns

To trim leading empty cells in excel, the operator is used like this:=A1.:E5

In EPPLUS, use the _TRO_LEADING function.

sheet.Cells["F1"].Formula = "_TRO_LEADING(A1:E5)";

Trim leading and trailing rows and columns

To trim both leading and trailing rows in excel, the operator can be used like this:A1.:.E5

In EPPLUS, use the _TRO_ALL(A1:E5) function.

sheet.Cells["F1"].Formula = "_TRO_ALL(A1:E5)";