TRIMRANGE

How to Use the TRIMRANGE Function in Excel

by functionstrim

Microsoft Excel has recently rolled out a new function, TRIMRANGE. And as its name suggests, it will help you trim a range, to ensure that your formula is efficient and isn’t including unnecessary blank values in calculations. Here’s a breakdown of the function.

The inputs for the TRIMRANGE function

There are three arguments to enter for the TRIMRANGE function:

Range: the range that you want to trim.

Trim_Rows: specify which rows to trim from the specified range. 0=no rows; 1=leading blank rows; 2=trailing blank rows; and 3=both leading and trailing blank rows (this is the default).

Trim_Columns: specify which columns to trim from the range. 0=no columns; 1=leading blank columns; 2=trailing blank columns; 3=both leading and trailing blank columns (default).

If you just want to trim everything, then you don’t need to worry about entering any arguments beyond just the initial range you want to trim.

How to function works

In the following example, I have values filled in only up until row 11:

Values in Excel filled up until row 11.

With the TRIMRANGE function, I can select the entire range A:B, and it will return an array of the values which only contain values. The formula only requires that one range.

=TRIMRANGE(A:B)

This is the result:

Result of the TRIMRANGE function.

If, however, there are gaps within the data set, then it will return zero values:

Result of the TRIMRANGE function when there are gaps within the range.

Where it performs well is when it excludes leading and trailing empty spaces:

Result of the TRIMRANGE function when there are gaps before or after a range.

If you like this post on How to Use the TRIMRANGE Function in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

Removing Blank Spaces

by with No Comment intermediatesubstitutetips & trickstrim
When copying data into Excel from other sources – be it web pages, documents, or other Excel documents sometimes you end up with unwanted formatting, such as numbers with invisible spaces that prevents it as being read as a number or converted into one. Or just ending up with data you don’t want.
The TRIM function in Excel is useful for removing trailing spaces after text that serve no purpose.
The more annoying issue that I’ve come across is a blank space that looks like one but isn’t. It is usually character # 160 which looks just like a blank space, only thinner than normal. If you enter char(160) in a cell you’ll see what this looks like. It can be a frustrating process because this character you can’t get rid of by just searching for blank characters and doing a find and replace or the trim function. It’s possible to see this with other characters as 160 isn’t the only one that looks like a blank space, but it is the only one I’ve come across so far.
The solution is to replace character 160 with character 32 (this is a normal space that will get eliminated with the TRIM function). To do this, use the following formula, assuming cell a1 is the cell that needs the cleanup:
=TRIM(SUBSTITUTE (A1,CHAR(160),CHAR(32)))
You can add a *1 to the end of the formula to convert it to a number if necessary.
See below for an example. The only difference between the cells in A2 and A3 is A2 has a normal blank space after it whereas cell A3 has character 160 after it. Both cells equal the same length (12 characters) as show in column B. column C is what the cells look after using the TRIM function – they will look the same but column D recalculates the length and the top cell has now gone down by one character (the trailing space). Column E is after the above formula is used. You can see now the updated length in column F is 11 for both, meaning the trailing character has been deleted in both cells.

As a side note, if you’re need to reference a specific character in excel you’ll notice all it takes is using the CHAR formula. If you’re not sure which number of the character you need, you can have the CHAR function reference the numbers 1 to 255 and you’ll see all the different characters available.