How to Use the TRIM Function in Google Sheets
Working with spreadsheets with excessive white space is annoying and can mess with certain functions' execution. But, you can fix this easily in Google Sheets using the TRIM function or the Trim whitespace shortcut in the menu. Read on to learn how.
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 could 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:
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:
Thetextparameter 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.
Using the function also allows you toimport data from a different sheetor a different part of the same spreadsheet. Let’s take a look at the latter. Here are the steps you need to follow:
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 handyautofill 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 forARRAYFORMULA in Google Sheetsis:
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:
Thetextparameter defines the range containing the text to divide, and thedelimiteris the character used to separate the text, like space or a hyphen.
In the below example, the text we wish to split is:
As you may 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:
How the Nested Formula Works
The completed formula in this particular example looks like this:
Once you press theEnterbutton, 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 ensure 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.
Want to get the most out of Google Sheets? Here’s how to use Google Sheets like a pro to get things done quickly and easily.
I gripped my chair the entire time—and then kept thinking about it when the screen turned off.
It saves me hours and keeps my sanity intact.
Turn these settings on, and your iPhone will be so much better than before.
Your iPhone forgets what you copy, but this shortcut makes it remember everything.
The key is not to spook your friends with over-the-top shenanigans.
