What Is the TRIM Function in Google Sheets?
There are frequently additional spaces added to the text when we copy or import text into a spreadsheet, which might impact the outcomes of your functions. You need to eliminate these gaps to deal with clean data.
You can use a TRIM formula to eliminate the extra spaces and tidy up your text instead of manually altering the data to remove them. TRIM can get rid of the text area’s leading, trailing, and repetitive spaces. After deleting the excess spaces, it produces a new text string based on the originally supplied string.
How to Use the Trim Whitespace Menu in Google Sheets
You may not even need to use the TRIM function to sort out your excess white spaces. You can often use the Trim whitespace shortcut, which is much simpler to use.
You can access this function by using only a few straightforward steps. Here is what you have to do:
Select the cells you wish to clean. The blue outline will indicate this. Click on Data in the main bar at the top of your spreadsheet. This will open a drop-down menu. Click on Data cleanup. Click on Trim whitespace.
After you click this option, Google Sheets will process the data in the selected cells and apply the changes. This option is great for one-time use as the formula isn’t applied to any cells, saving unnecessary calculations and keeping the spreadsheet from becoming slow.
TRIM Function Syntax
If the Trim whitespace menu shortcut doesn’t do what you need, you’ll need to use the TRIM function instead. The formula for TRIM in Google Sheets is fairly basic, as it only uses one parameter. Here is the syntax:
The text parameter in this formula defines the text that needs trimming or its cell address. To leave individual spaces between the words, TRIM eliminates all gaps before the initial character, the spaces after the last character, and all duplicated spaces between them.
How to Use the TRIM Function in Google Sheets
Using the function also allows you to import data from a different sheet or a different part of the same spreadsheet. Let’s take a look at the latter. Here are the steps you need to follow:
Click on the cell where you wish to write the formula. In this case, it is cell D2. Type an Equal (=) sign to begin the formula. Type the beginning part of the formula, which is TRIM(. Type in the address for the cell on which you want to use the TRIM function. It is better to use the first cell here as you can then use Autofill. We will explain more about that below. In this case, we used cell A2. Add the closing bracket to finish off the formula. Press the Enter button to execute the formula.
You will see the value of the added cell show up with all the unnecessary spaces removed. But, you most likely don’t have just one value to sort through. Spreadsheets can contain thousands of entries for one column, and it’s just not realistic to sort through all of them one by one. Instead, we can use the handy autofill feature in Google Sheets.
To use it, select the cell, click, and drag the blue dot in the bottom right corner of the box. Drag it towards the direction of the data. The formula will automatically be applied to all the cells there.
Advanced Use Case: Making a Nested TRIM Function in Google Sheets
Like with most other functions in Google Sheets, you can also use TRIM alongside other functions. To demonstrate this, let’s look at how we can use the ARRAYFORMULA, SPLIT, and TRIM functions to clean the data and sort it into different cells. Before we start, let’s look at the syntax for these formulas.
The syntax for ARRAYFORMULA in Google Sheets is:
The parameter can be a range or an expression that uses one or more cell ranges or a function whose result can be greater than one cell.
The syntax for SPLIT in Google Sheets is:
The text parameter defines the range containing the text to divide, and the delimiter is the character used to separate the text, like space or a hyphen.
In the below example, the text we wish to split is:
Diane Barrett , Monica Webster , Travis Lopez , Lawrence Briggs , Steven Rose , Connie West , Luz Herrera
As you can see, there are extra spaces, and all the text appears in a single cell. Here is how to use nested functions to split the data:
Click the cell where you want the first name to appear, B3 in the above example. Type in an Equal (=) symbol to start the formula. The first part of the formula will be an array, so type ARRAYFORMULA( first. Add the formula for TRIM. Type TRIM( here. Add SPLIT( to start the SPLIT formula. Here, we will add the two parameters needed for the SPLIT formula. In the above example, we typed in A1 as the text argument, which is the cell address containing the names. Add a comma. Type in the delimiter in quotation marks, which will look like “,” Now add the closing brackets to finish off the formula. Note: When you have a nested formula, you must add the same number of closing brackets as the number of functions in the formula. In the above example, that’s three brackets. Press Enter.
How the Nested Formula Works
The completed formula in this particular example looks like this:
Once you press the Enter button, you will see that the names split up correctly, with the comma as a splitting point (the delimiter). TRIM will remove the spaces before and after the names, and ARRAYFORMULA will make sure that the names are added in their proper cells, i.e., make them into an array.
Using the TRIM Function in Google Sheets
Although it may not come up often that you have to use nested TRIM functions, copied data frequently have unwanted spaces. So, you should familiarize yourself with the Trim whitespace menu shortcut and the TRIM function as a stepping stone to improving your Google Sheets.
