The TRIM function in Google Sheets is a nifty little tool that is especially useful for cleaning up those unnecessary spaces in your spreadsheets. Here, we will be learning how to use TRIM function with SPLIT in Google Sheets. Show
Table of ContentsThe I’m sure you have encountered data in Google Sheets with the odd leading spaces that throws the alignment off, the unnoticed trailing spaces when you copy the data externally or the occasional double space from a typo. 😱 In this guide, we will be teaching you not only how to use the Now, imagine that you were planning a birthday party and created a public Google Sheets for the invitees to RSVP and provide their details. Unfortunately, as mentioned above, typos happen, and you notice that the spreadsheet has several unnecessary spaces in the data. So how do we remove these spaces? Easy. 😉 We use the Now, let’s move on to the next section to see how to use the
How to Use the TRIM FunctionThere are two ways you can use this function. The first of which is already built into the Google Sheets menu. The first method is to simply clean up any unnecessary spaces in your selected data using the Google Sheets menu. This can be accomplished by accessing the Google Sheets Menu>Data and clicking on “Trim Whitespace”.
At this point, Google Sheets will instantly go through the text in every selected cell and look for any spaces that occur at the beginning of the text, after the end of the text as well as any whitespaces other than the single space between words. Keep in mind that this directly overwrites the data in the selected cells. Now, the second method of using the =TRIM(text) Let’s break down this function:
Now that you have an idea on how the
A Real Example of Using the TRIM FunctionUsing our initial example of a spreadsheet tracking a birthday party’s guest information, we will be using the below data to demonstrate how to use the
As you can see, we have several unfortunate typos in the data. To clean this up, first, highlight the entire “Name” column. Next, access the Google Sheets Menu>Data and click on the TRIM function. Voila!🎉🎉🎉 Google Sheets has now removed all those annoying additional white spaces in the data. Alternatively, if you prefer to use the function, select the cell where you would like the cleaned data to be displayed. For this example, we are going to input our function in cell D2. Next, we input the syntax from the previous section “=TRIM(text)”. Thirdly, for this example, we will input cell A2 as the text attribute. Finally, hit the Enter key and Google Sheets will have removed the extra white space in between Emma Clarkson’s name. Simple right? You can make a copy of the spreadsheet using the link I have attached below Make a copy of example spreadsheet.
A Real Example of Using the TRIM Function With SPLITBefore we begin to learn how to use the A more relevant example would be to imagine that the party’s guest list was sent to you in this format: “Emma Clarkson, Olivia Charles, Ava Watson, Isabella Rodriguez, Amelia Parker, Charlotte Wong, Harper Lee, Sophia Adams” Normally, it would take a lot of time and effort just to put each individual name into their respective cells. Now, imagine if the guest list numbered in the hundreds. That’s where the Again, if you would like to learn the The data below will be used for this example.
As mentioned above, the first step in handling this data would be to use the For this example, we will be using the =SPLIT(A1, “,”) As expected, Google Sheets splits the names into cells B4 to I4. However, as you can see, after the splitting, the data in cells C4 – I4 all have a whitespace at the beginning of the data. This is due to the fact that the delimiter does not include the whitespace behind each comma. To remove these whitespaces, you would normally be able to use the However, if you try to use the formula =TRIM(SPLIT(A1,”,”)), this happens:
You end up with just the first name, “Emma Clarkson” in cell B4 with cells C4 – I4 completely empty. This happens because when you use the That is why we need to utilize the Our new formula becomes =ARRAYFORMULA(TRIM(SPLIT(A1,”,”))) Once you hit enter, you will see that the names are split correctly and the It might sound a little complicated but don’t worry, we will provide a step to step guide in the next section. Before that, if you’d like to try the formula yourself, you can grab a copy of the example data from the link below: Make a copy of example spreadsheet.
How to Use the TRIM Function in Google Sheets
How to Use the TRIM Function with SPLIT in Google Sheets
And that’s it! Great job for completing the guide 👍. You can now use the
Get emails from us about Google Sheets.Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content. There will be no spam and you can unsubscribe at any time. How do I extract part of a text string in Google Sheets?How Do I Extract Specific Text From a Cell in Google Sheets? You can use LEFT + SEARCH in Google Sheets to extract text from a string or to extract data that comes before a specific text. LEFT is used to return a specific number of characters from the leftmost cell's beginning.
How do I trim a cell value in Google Sheet?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).
How do I trim columns in Google Sheets?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.
|