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.

rangeThe 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

 ABC
1=TRIMRANGE(B1:B3) = { one }  
2=TRIMRANGE(B1:B3,1) = { one ; }one30
3=TRIMRANGE(B1:B3,2) = { ; one}  
4=TRIMRANGE(B1:B3,3) = { one }two20
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

© 2025 Better Solutions Limited. All Rights Reserved. © 2025 Better Solutions Limited Top