TRIMRANGE Function

Description

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

NOTE

• The returned result can be an array or a reference.

Syntax

Copied!=TRIMRANGE(range, [trim_rows], [trim_cols])
=TRIMRANGE(range, [trim_rows], [trim_cols])

SAMPLE data

|     |  A  |   B    |   C  | D   | E   |
|-----|-----|--------|------|-----|-----|
|  1  |     |        |      |     |     |
|-----|-----|--------|------|-----|-----|
|  2  |     |        |      |     |     |
|-----|-----|--------|------|-----|-----|
|  3  |     | Orange |   1  |     |     |
|-----|-----|--------|------|-----|-----|
|  4  |     | Apple  |   2  |     |     |
|-----|-----|--------|------|-----|-----|
|  5  |     | Lemon  |   3  |     |     |
|-----|-----|--------|------|-----|-----|
|  6  |     |        |      |     |     |
|-----|-----|--------|------|-----|-----|
|  7  |     |        |      |     |     |

EXAMPLE 1

Copied!//[𝘰𝘱𝘵𝘪𝘰𝘯𝘢𝘭]: 𝘵𝘳𝘪𝘮_𝘳𝘰𝘸𝘴=3 [0,3], 𝘵𝘳𝘪𝘮_𝘤𝘰𝘭𝘴=3 [0,3]
//𝘵𝘳𝘪𝘮 𝘭𝘦𝘢𝘥𝘪𝘯𝘨 𝘢𝘯𝘥 𝘵𝘳𝘢𝘪𝘭𝘪𝘯𝘨 𝘳𝘰𝘸𝘴.
=TRIMRANGE(B1:B7) -> return B3:B5
//[𝘰𝘱𝘵𝘪𝘰𝘯𝘢𝘭]: 𝘵𝘳𝘪𝘮_𝘳𝘰𝘸𝘴=3 [0,3], 𝘵𝘳𝘪𝘮_𝘤𝘰𝘭𝘴=3 [0,3]
//𝘵𝘳𝘪𝘮 𝘭𝘦𝘢𝘥𝘪𝘯𝘨 𝘢𝘯𝘥 𝘵𝘳𝘢𝘪𝘭𝘪𝘯𝘨 𝘳𝘰𝘸𝘴.
=TRIMRANGE(B1:B7) -> return B3:B5

example 2

Copied!//𝘵𝘳𝘪𝘮 𝘵𝘳𝘢𝘪𝘭𝘪𝘯𝘨 𝘣𝘭𝘢𝘯𝘬 𝘳𝘰𝘸𝘴.
=TRIMRANGE(B1:B7, 2) -> return B1:B5
//𝘵𝘳𝘪𝘮 𝘵𝘳𝘢𝘪𝘭𝘪𝘯𝘨 𝘣𝘭𝘢𝘯𝘬 𝘳𝘰𝘸𝘴.
=TRIMRANGE(B1:B7, 2) -> return B1:B5

Copied!//𝘵𝘳𝘪𝘮 𝘭𝘦𝘢𝘥𝘪𝘯𝘨 𝘢𝘯𝘥 𝘵𝘳𝘢𝘪𝘭𝘪𝘯𝘨 𝘣𝘰𝘵𝘩 𝘣𝘭𝘢𝘯𝘬 𝘳𝘰𝘸𝘴, 𝘤𝘰𝘭𝘶𝘮𝘯𝘴 𝘶𝘴𝘦 𝘛𝘙𝘐𝘔 𝘙𝘌𝘍𝘌𝘙𝘌𝘕𝘊𝘌.
=A1.:.E7 -> return B3:B5
//𝘵𝘳𝘪𝘮 𝘭𝘦𝘢𝘥𝘪𝘯𝘨 𝘢𝘯𝘥 𝘵𝘳𝘢𝘪𝘭𝘪𝘯𝘨 𝘣𝘰𝘵𝘩 𝘣𝘭𝘢𝘯𝘬 𝘳𝘰𝘸𝘴, 𝘤𝘰𝘭𝘶𝘮𝘯𝘴 𝘶𝘴𝘦 𝘛𝘙𝘐𝘔 𝘙𝘌𝘍𝘌𝘙𝘌𝘕𝘊𝘌.
=A1.:.E7 -> return B3:B5

Copied!//𝘳𝘦𝘵𝘶𝘳𝘯𝘴 𝘢𝘯𝘰𝘵𝘩𝘦𝘳 𝘳𝘦𝘧𝘦𝘳𝘦𝘯𝘤𝘦.
=OFFSET(TRIMRANGE(B1:B6), 1, 1, 2) -> return C4:C5
//𝘳𝘦𝘵𝘶𝘳𝘯𝘴 𝘢𝘯𝘰𝘵𝘩𝘦𝘳 𝘳𝘦𝘧𝘦𝘳𝘦𝘯𝘤𝘦.
=OFFSET(TRIMRANGE(B1:B6), 1, 1, 2) -> return C4:C5

RELATED