Learn how to use the TRIM function in Google Sheets in this simple step by step tutorial. When you import or copy text data into a spreadsheet, there are usually extra spaces included in the text that might affect the results of your functions. In order to work with clean data, you need to get rid of these spaces. Rather than manually editing the data to delete the unwanted spaces, use the TRIM function to eliminate them and clean up your text. What is the TRIM function?The TRIM function removes leading, trailing, and repeated spaces in the text. It returns a string based on a given string, after removing the extra spaces. The way you can use the TRIM function is:
The function has one argument, text. This is the string that you would like to trim. As a result, the TRIM function removes all spaces before the first character, all spaces after the last character, and all duplicate spaces between characters, leaving only single spaces between the words. When to use TRIM for marketing stuff?
How To Use The TRIM Function in Google SheetsYou can define the text argument in two ways:
Direct text value
Cell reference
In this second example, you’ll need to create a new column or row in the sheet for your clean text, but often you only need the trimmed text. Then you can copy and paste the
clean text back to the original column to replace it. How to Trim Whitespace in Google SheetsThere is a quick alternative way to remove the extra spaces from a cell by using the Trim whitespaces tool. You just need to click on the cell or cells with the text to be trimmed and find the Data > Trim whitespace option in the header menu: This tool will immediately remove the extra space characters from the selected cell(s). Even though this simple tool exists to remove extra spaces, the TRIM function has more potential when you use it in combination with other functions… TRIM Formula ExampleFor example, you can include it in your formulas to check for duplicate entries, to search for a specific term or to avoid false results when working with numbers. Whenever the possible extra spaces may cause errors, you can solve this issue by completing your textual or numeric input values with a wrapping TRIM function inside your other functions. Here is an example of two (bacon-flavoured) texts that are almost the same, but one has extra spaces. Applying the EQ (equality) formula would normally return FALSE as the result, even though the two texts only differ in the number of spaces.
You can use a wrapping TRIM function for every text argument in the function. This way the formula will work with clean data and ignore the spaces when checking for the textual equality. Similarly, you can add the TRIM function to any other formulas to make sure that unwanted spaces will not manipulate your results. 🔥 TIP: Use CLEAN & TRIM to Tidy Your Data
Check out more tips like this here → Google Sheets Tips SummaryThere you have it, a simple but useful Google Sheets formulas for tidying up data. You might also like these resources:
How do I trim white space in Google Sheets?To trim whitespaces:
Select a cell or range of data that contains whitespace on your sheet. From the toolbar, select Data > Trim whitespace. You'll then see a dialogue box that details how many cells whitespace was removed from.
How do you remove spaces in cells Google Sheets?If you want to remove the spaces within your text and keep that data in the existing cells, you can use the handy Trim Whitespace feature. Select the cells with the text you want to remove the spaces from. Then click Data > Trim Whitespace from the menu.
How do I trim a value in Google Sheets?TRIM is a function in Google Sheets that removes all spaces from text except for single spaces between words. This can be useful when cleaning up data or text that has been copied and pasted from other sources. The TRIM function can be accessed by typing =TRIM( in the cell where you want the text to be cleaned up.
|