J
JoeMcDaid
I’m excited to announce a new function, TRIMRANGE, and a set of new reference operators.
NOTE: This is a preview function. Its signature and results may change substantially before being broadly released, based on your feedback. So, we do not recommend using it in important workbooks until it is generally available.
We’re introducing a new function and a set of supporting references:
The TRIMRANGE function removes empty rows from the edges of a range. This can be particularly useful when writing dynamic array formulas or optimizing lambda functions for performance.
In the example below, we use TRIMRANGE to calculate the length of any text entered into column A.
Calculate the length of text entered into column A with TRIMRANGE
Without the use of TRIMRANGE, =LEN(A:A) would run on every cell, returning over a million unnecessary results. Besides being inefficient, trailing undesirable 0’s are returned to the grid. This can be especially problematic if you then try and operate on the spill using =C1# notation.
TRIMRANGE is also a great new tool for optimizing the performance of lambda functions that operate on ranges. It allows lambda authors to more tightly scope ranges, which can reduce the number of necessary computations.
More information on the TRIMRANGE function can be found on the help page.
Trim References (‘Trim Refs’) offer a more succinct way to call common variances of TRIMRANGE. They are a modifier of the iconic colon range operator A1:E5. By prefixing or suffixing the colon with a period, you can request to trim blanks from the start, end or both.
Full-column references are often avoided because they can have poor performance with some functions. However, with trim refs, they are much more performant as the full-column reference can be constrained to just the portion with values.
In the example below, we use the trailing trim ref to trim a full column reference. We expect trailing trim refs to be the most commonly used of the 3 trim ref variants.
Trim a full column reference with trailing trim ref
This new function and new references are currently available to Beta Channel users running Version 2409 (Build 18020.2000) or later.
Don’t have it yet? It’s probably us, not you.
Features are released over some time to ensure things are working smoothly. We highlight features that you may not have because they’re slowly releasing to larger numbers of Insiders. Sometimes we remove elements to further improve them based on your feedback. Though this is rare, we also reserve the option to pull a feature entirely out of the product, even if you, as an Insider, have had the opportunity to try it.
We want to hear from you! Please click Help > Feedback in Excel on Windows to submit your thoughts about these new functions.
Learn about the Microsoft 365 Insider program and sign up for the Microsoft 365 Insider newsletter to get the latest information about Insider features in your inbox once a month!
Continue reading...
NOTE: This is a preview function. Its signature and results may change substantially before being broadly released, based on your feedback. So, we do not recommend using it in important workbooks until it is generally available.
New TRIMRANGE function and trim refs: easily remove blanks from the edges of a range
We’re introducing a new function and a set of supporting references:
- TRIMRANGE function – a function that removes empty rows from the edges of a range.
- Trim references – offer a more succinct way to call common variations of the TRIMRANGE function.
TRIMRANGE function
The TRIMRANGE function removes empty rows from the edges of a range. This can be particularly useful when writing dynamic array formulas or optimizing lambda functions for performance.
In the example below, we use TRIMRANGE to calculate the length of any text entered into column A.
Calculate the length of text entered into column A with TRIMRANGEWithout the use of TRIMRANGE, =LEN(A:A) would run on every cell, returning over a million unnecessary results. Besides being inefficient, trailing undesirable 0’s are returned to the grid. This can be especially problematic if you then try and operate on the spill using =C1# notation.
TRIMRANGE is also a great new tool for optimizing the performance of lambda functions that operate on ranges. It allows lambda authors to more tightly scope ranges, which can reduce the number of necessary computations.
More information on the TRIMRANGE function can be found on the help page.
Trim References
Trim References (‘Trim Refs’) offer a more succinct way to call common variances of TRIMRANGE. They are a modifier of the iconic colon range operator A1:E5. By prefixing or suffixing the colon with a period, you can request to trim blanks from the start, end or both.
| Type | Example | Equivalent TRIMRANGE | Description |
| Trailing trim ref  .) | A1:.E10 | TRIMRANGE(A1:E10,2,2) | Trim trailing blanks |
| Leading trim ref (. | A1.:E10 | TRIMRANGE(A1:E10,1,1) | Trim leading blanks |
| Full trim ref (.:.) | A1.:.E10 | TRIMRANGE(A1:E10,3,3) | Trim leading and trailing blanks |
Full-column references are often avoided because they can have poor performance with some functions. However, with trim refs, they are much more performant as the full-column reference can be constrained to just the portion with values.
In the example below, we use the trailing trim ref to trim a full column reference. We expect trailing trim refs to be the most commonly used of the 3 trim ref variants.
Trim a full column reference with trailing trim refAvailability
This new function and new references are currently available to Beta Channel users running Version 2409 (Build 18020.2000) or later.
Don’t have it yet? It’s probably us, not you.
Features are released over some time to ensure things are working smoothly. We highlight features that you may not have because they’re slowly releasing to larger numbers of Insiders. Sometimes we remove elements to further improve them based on your feedback. Though this is rare, we also reserve the option to pull a feature entirely out of the product, even if you, as an Insider, have had the opportunity to try it.
Feedback
We want to hear from you! Please click Help > Feedback in Excel on Windows to submit your thoughts about these new functions.
Learn about the Microsoft 365 Insider program and sign up for the Microsoft 365 Insider newsletter to get the latest information about Insider features in your inbox once a month!
Continue reading...