Announcing TRIMRANGE and accompanying trim references

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.

[HEADING=1]New TRIMRANGE function and trim refs: easily remove blanks from the edges of a range[/HEADING]

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.
  

[HEADING=3]TRIMRANGE function[/HEADING]

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.

[ATTACH type=full" alt="Calculate the length of text entered into column A with TRIMRANGE]64010[/ATTACH]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.

[HEADING=3]Trim References[/HEADING]

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.

[ATTACH type=full" alt="Trim a full column reference with trailing trim ref]64011[/ATTACH]Trim a full column reference with trailing trim ref

[HEADING=2]Availability[/HEADING]

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.

[HEADING=2]Feedback[/HEADING]

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...