Function TRIMGRANGE

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.

The function syntax looks like this:

TRIMRANGE(<range>,[<rows>],[<columns>])

The first argument is the range in which the blank values need to be removed.

When you want to extract unique values from a given range of data, you will use the UNIQUE function. If you want to edit these values, you’ll combine it with the SORT function. Formula:

=SORT(UNIQUE(A3:A1000))

However, if you include a little more values in a range than there are the function will, besides distinct values, return zero.

Frame this expression with the TRIMRANGE function, and all empty values will be removed.

The second, optional argument of this function, can have the following values: 0 (do not remove blank values), 1 (remove blank values at the beginning of a range), 2 (remove blank values at the end of a range), or 3 (remove blank values at the beginning or end). The third, also optional, argument contains respective values, only they refer to the ranges in which the columns are defined.

For example, if we write the formula:

=TRIMRANGE(SORT(UNIQUE(A3:A1000)),1)

You will see that Excel will not remove blank values, because we insist that they be removed at the beginning.

I’m going to change the formula to:

=TRIMRANGE(SORT(UNIQUE(A3:A1000)),2)

Blank values will be removed, as they are removed from the end of the range.