The TRIMRANGE function excludes all empty rows and/or columns from the outer edges of a range or array.
Syntax
The TRIMRANGE function scans in from the edges of a range or array until it finds a non-blank cell (or value), it then excludes those blank rows or columns.
=TRIMRANGE(range,[trim_rows],[trim_cols])
Argument | Description |
|---|---|
range Required | The range (or array) to be trimmed
|
trim_rows | 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 | 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) |
Trim References (aka Trim Refs)
A Trim Ref can be used to achieve the same functionality as TRIMRANGE more succinctly by replacing the range’s colon “:” with one of the three Trim Ref types described below:
Type | Example | Equivalent TRIMRANGE | Description |
|---|---|---|---|
Trim All (.:.) | A1.:.E10 | TRIMRANGE(A1:E10,3,3) | Trim leading and trailing blanks |
Trim Trailing (:.) | A1:.E10 | TRIMRANGE(A1:E10,2,2) | Trim trailing blanks |
Trim Leading (.:) | A1.:Z10 | TRIMRANGE(A1:E10,1,1) | Trim leading blanks |
This pattern can also be applied to full-column or -row references (eg. A:.A)
