Regular expressions are a programming term that refers to a sequence of characters used to define a search criterion within a given text. Recently, Excel has gained several functions for working with regular expressions, and we can also use them as an argument to the XMATCH function and the XLOOKUP function as an additional search criterion.
The TRIMRANGE function allows you to remove blank rows or columns before, after, and before and after a set of values within a specified range of cells. It makes it easier to work with cells that are not empty, but recently there is a much simpler syntax for clearing a given range, which is by specifying a dot before the “:” symbol that separates the beginning and end of the range.
There are several ways to use the Data Validation option to generate lists to select values when filling out tables. One of the earlier “recipes” was about how to create dynamic lists. With the advent of the TRIMRANGE function, this can now be done in an even simpler way, without having to use a Name Manager and write complicated formulas.
One of the earlier “recipes” was about how to use the Go To Special option to remove empty rows from the table. Now it is possible to do this in a much easier way, by using the TRIMRANGE function in combination with some other functions which are returning dynamic arrays. The result is a dynamic table that will be automatically updated if you decide to remove any more rows.
The TRIM function removes extra blank characters, we all know this by now, and TRIMRANGE, as its name suggests, is used to remove empty values from a selected range of data. In addition, it is possible to specify whether their removal takes place in rows or columns and in which order. It is well combined with the SORT and UNIQUE functions, and in the following text you will see how to apply it in practice.