TRIMRANGE

TRIMRANGE(range,[row_trim_mode],[col_trim_mode])

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

range	The range (or array) to be trimmed.
row_trim_mode	(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)
col_trim_mode	(Optional) Determines which columns should be trimmed: 0 = None 1 = Trims leading blank rows 2 = Trims trailing blank rows 3 = Trims both leading and trailing blank rows (default)

REMARKS

* For an illustrated example refer to the page under Data Functions
* This function was added in Microsoft 365 (version 2504, first released October 2024).
* This function can return multiple values.
* The same functionality can also be achieved using the "Trim References".
* The Trim Reference (.:) removes leading blanks.
* The Trim Reference (:.) removes trailing blanks.
* The Trim Reference (.:.) removes both leading and trailing blanks.
* This function can be particularly useful when writing dynamic array formulas or optimizing lambda functions for performance.
* Using the trim references any full-column references can be constrained to just the portion with values.
* For the Microsoft documentation refer to support.microsoft.com

	A	B	C
1	=TRIMRANGE(B1:B3) = { one }
2	=TRIMRANGE(B1:B3,1) = { one ; }	one	30
3	=TRIMRANGE(B1:B3,2) = { ; one}
4	=TRIMRANGE(B1:B3,3) = { one }	two	20
5	=B1.:B3 = { one ; }
6	=B1:.B3 = { ; one }
7	=B1.:.B3 = { one }

1 - Remove the leading and trailing blanks
2 - Remove the leading blanks
3 - Remove the trailing blanks
4 - Remove the leading and trailing blanks
5 - Remove the leading blanks
6 - Remove the trailing blanks
7 - Remove the leading and trailing blanks