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)
