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.
Table of Contents
TRIM function one of many built-in functions in Google Sheets that are designed to both clean up the data you put into your spreadsheet.
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
TRIM Function but also how to combine it with the
SPLIT Function. Before we begin, however, if you would like to learn more about the
SPLIT Function, you can find a detailed guide here.
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
TRIM format that automatically detects these spaces and removes them summarily.
Now, let’s move on to the next section to see how to use the
TRIM function in Google Sheets.
How to Use the TRIM Function
There 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 function is to use this formula:
Let’s break down this function:
=is the equal sign and it is how we start any function in Google Sheets.
TRIM()will be our function. For it to be operational, we need to add the
textattribute inside the parenthesis.
textis the cell or range of cells that you would like to apply the
Now that you have an idea on how the
TRIM function works, we’ll move on to some examples in the next section.
A Real Example of Using the TRIM Function
Using 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.
You can make a copy of the spreadsheet using the link I have attached below
A Real Example of Using the TRIM Function With SPLIT
Before we begin to learn how to use the
TRIM function with
SPLIT in Google Sheets, let’s do a quick recap of the
SPLIT function. In Google sheets, the
SPLIT function is used to separate text in a cell by a common delimiter into separate columns.
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
SPLIT function comes in. By using the comma symbol “,” as the delimiter, the
SPLIT function will separate each name into a separate column saving you a lot of time.
Again, if you would like to learn the
SPLIT function in more detail, you can find Sheetaki’s step-by-step guide here.
The data below will be used for this example.
As mentioned above, the first step in handling this data would be to use the
SPLIT function to separate the names into separate cells.
For this example, we will be using the
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
TRIM function together with 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
SPLIT function, the result is an array and not just a single cell. As the
TRIM Function only works on a single cell at a time, only the first cell in the array is processed and displayed by the
That is why we need to utilize the
=ARRAYFORMULA() function together with our existing formula. The
ARRAYFORMULA function allows us to use non-array functions like
TRIM with arrays. For those of you not familiar with how to use the
ARRAYFORMULA function, you can check out our detailed guide here.
Our new formula becomes
Once you hit enter, you will see that the names are split correctly and the
TRIM Function has removed all the leading whitespaces.
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:
How to Use the TRIM Function in Google Sheets
- First, click on an empty cell to activate it.
- Next, type the equals sign “=” to let Google Sheets know that you are going to input a formula.
- Thirdly, you want to type
TRIMto use the function.
- Remember to key in an open parenthesis “(“ here before inputting the
- For the
textattribute, we can select the cell A3 with our cursor.
- Alternatively, you can also type it in manually.
- Hit the Enter key, and all the unnecessary whitespaces will be removed.
How to Use the TRIM Function with SPLIT in Google Sheets
- First, click on an empty cell to activate it
- Next, type the equals sign “=” to let Google Sheets know that you are going to input a formula
- Here, you will input
ARRAYFORMULAas the first function we will be using in this formula.
- Input the first open parenthesis “(“.
- Follow that with the second function,
TRIM, and another open parenthesis “(“.
- Here, in place of the
textattribute, we will input our third and final function in the formula,
- Input yet another open parenthesis “(“ and Google Sheets will display the attributes that you need to input next for the
SPLITfunction to work.
- Next, we need to input the
textattribute of the
SPLITfunction. You can just click on the cell containing the data (A1) you would like to split or key it in manually.
- Remember to add a comma symbol “,” here before you enter the
- For this example, we will be splitting the text using the commas “,” in the text as the
delimiterattribute. Make sure to always wrap the
delimitervalue with double quotation marks “”.
- Hit the Enter key and the
TRIMfunction will have separated the text while the
TRIMfunction will have removed any of the leading whitespaces in the array’s data.
And that’s it! Great job for completing the guide 👍. You can now use the
TRIM function with
SPLIT in order to help you with your day-to-day Google Sheets work. You can even combine the TRIM function with other numerous Google Sheets formulas to make your life even easier!
Thanks a Lot for your time!!! Works perfect for what I needed.