Formula Reference
Contacting Us
Getting Technical Support
Formula Overview
Formula Functions
- Functions A to C
- Functions D to G
- Functions H to L
- Functions M to Q
- Functions R to S
- Functions T to Z
  - T
  - T.DIST
  - T.DIST.2T
  - T.DIST.RT
  - T.INV
  - T.INV.2T
  - T.TEST
  - TAKE
  - TAN
  - TANH
  - TBILLEQ
  - TBILLPRICE
  - TBILLYIELD
  - TDIST
  - TEXT
  - TEXTAFTER
  - TEXTBEFORE
  - TEXTJOIN
  - TEXTSPLIT
  - TIME
  - TIMEVALUE
  - TINV
  - TOCOL
  - TODAY
  - TOROW
  - TRANSPOSE
  - TREND
  - TRIM
  - TRIMMEAN
  - TRIMRANGE
  - TRUE
  - TRUNC
  - TTEST
  - TYPE
  - UNICHAR
  - UNICODE
  - UNIQUE
  - UPPER
  - USDOLLAR
  - VALUE
  - VALUETOTEXT
  - VAR
  - VAR.P
  - VAR.S
  - VARA
  - VARISPARKLINE
  - VARP
  - VARPA
  - VBARSPARKLINE
  - VDB
  - VLOOKUP
  - VSTACK
  - WEBSERVICE
  - WEEKDAY
  - WEEKNUM
  - WEIBULL
  - WEIBULL.DIST
  - WINLOSSSPARKLINE
  - WORKDAY
  - WORKDAY.INTL
  - WRAPCOLS
  - WRAPROWS
  - XIRR
  - XLOOKUP
  - XMATCH
  - XNPV
  - XOR
  - YEAR
  - YEARFRAC
  - YEARSPARKLINE
  - YIELD
  - YIELDDISC
  - YIELDMAT
  - Z.TEST
  - ZTEST

TRIMRANGE

This function excludes all empty rows and/or columns from the outer edges of a range or array.

Syntax

TRIMRANGE(range,[trim_rows],[trim_cols])

Arguments

The TRIMRANGE function scans in from the edges of a range or array until it finds a non-blank cell (or value), then excludes those blank rows or columns.

Argument	Description
range	[Required] The range (or array) to be trimmed.
trim_rows	[Optional] Determines which rows should be trimmed. 0 – None 1 – Trims leading blank rows 2 – Trims trailing blank rows 3 – Trims both leading and trailing blank rows (default)
trim_columns	[Optional] Determines which columns should be trimmed. 0 – None 1 – Trims leading blank columns 2 – Trims trailing blank columns 3 – Trims both leading and trailing blank columns (default)

Argument

Description

range

[Required] The range (or array) to be trimmed.

trim_rows

[Optional] Determines which rows should be trimmed.

0 – None

1 – Trims leading blank rows

2 – Trims trailing blank rows

3 – Trims both leading and trailing blank rows (default)

trim_columns

[Optional] Determines which columns should be trimmed.

0 – None

1 – Trims leading blank columns

2 – Trims trailing blank columns

3 – Trims both leading and trailing blank columns (default)

Remarks

Return: RangeReference. A trimmed range.

TRIMRANGE does not work with 3D references like =Sheet1:Sheet3!A:A. Spread.NET will auto-correct the formula =SUM(Sheet1:Sheet3!A.:.A) to =SUM(Sheet1:Sheet3!A:A)

The .:. :. .: are not operators, so the =A1.:.INDIRECT("A10") will be an invalid formula. And the =TRIMRANGE(A1:INDIRECT("A10")) can be used.

Trim References

Trim References (aka Trim Refs) are a set of shorthand notations that allow you to trim blank rows and columns from a range without having to use the TRIMRANGE function explicitly.

Trim Refs modify the traditional colon (:) range operator by adding dots (.) to specify how you want to trim the range. There are three types of Trim Refs:

Trim Ref Type	Syntax	Equivalent TRIMRANGE	Description
Trim All	`A1.:.E10`	`=TRIMRANGE(A1:E10, 3, 3)`	Trims both leading and trailing blanks from the range.
Trim Trailing	`A1:.E10`	`=TRIMRANGE(A1:E10, 2, 2)`	Trims only the trailing blanks from the range.
Trim Leading	`A1.:E10`	`=TRIMRANGE(A1:E10, 1, 1)`	Trims only the leading blanks from the range.

Examples

TRIMRANGE(A1:E10, 3, 3)

TRIMRANGE(A1:E10, 2, 2)

Version Available

This function is available in product version 19.0 or later.