Syntax
=TRIMRANGE(range, [trim_rows], [trim_cols])Arguments
| Argument | Required | Description |
|---|---|---|
range | Required | The range to trim |
trim_rows | Optional | 0 none, 1 leading, 2 trailing, 3 both (default) |
trim_cols | Optional | 0 none, 1 leading, 2 trailing, 3 both (default) |
How to use TRIMRANGE
When TRIMRANGE returns more than one value, the result spills into the cells below and to the right. Keep those cells empty, or you get #SPILL!.
TRIMRANGE takes 1 required argument and 2 optional arguments. Type =TRIMRANGE( in a cell and SheetsBat shows the arguments as you go. Optional arguments are shown in square brackets; leave them out to use their default.
Examples
Each result below was calculated by the SheetsBat formula engine.
=TRIMRANGE(A1:C5)Result Data in the sheet
| A | B | |
|---|---|---|
| 2 | 1 | |
| 3 | 2 |
Spilled result
| A | B | |
|---|---|---|
| 1 | 1 | 0 |
| 2 | 0 | 2 |
=ROWS(TRIMRANGE(A1:A10))Result Data in the sheet
| A | |
|---|---|
| 3 | 1 |
| 4 | |
| 5 | 2 |
Related array functions
- SORTBYSorts a range or array by the values of other ranges or arrays.
- SORTNReturns the first n rows of a range after sorting, with options for ties.
- TAKEReturns a number of rows or columns from the start (positive) or end (negative) of an array.
- TOCOLReturns the values of an array as a single column.
- TOROWReturns the values of an array as a single row.
- UNIQUEReturns the unique rows (or columns) of a range or array.
- VSTACKStacks arrays on top of each other; shorter rows are padded with #N/A.
- WRAPCOLSWraps a row or column of values into columns of the given length.
- WRAPROWSWraps a row or column of values into rows of the given length.