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
The 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:
=TRIM(text)
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 thetext
attribute inside the parenthesis.text
is the cell or range of cells that you would like to apply theTRIM
function on.
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 TRIM
function.
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
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 SPLIT
formula
=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 TRIM
function together with the SPLIT
function.
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 TRIM
function.
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
=ARRAYFORMULA(TRIM(SPLIT(A1,”,”)))
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
TRIM
to use the function.
- Remember to key in an open parenthesis “(“ here before inputting the
text
.
- For the
text
attribute, 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
ARRAYFORMULA
as 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
text
attribute, we will input our third and final function in the formula,SPLIT
.
- Input yet another open parenthesis “(“ and Google Sheets will display the attributes that you need to input next for the
SPLIT
function to work.
- Next, we need to input the
text
attribute of theSPLIT
function. 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
delimiter
attribute.
- For this example, we will be splitting the text using the commas “,” in the text as the
delimiter
attribute. Make sure to always wrap thedelimiter
value with double quotation marks “”.
- Hit the Enter key and the
TRIM
function will have separated the text while theTRIM
function 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!

1 comment
Thanks a Lot for your time!!! Works perfect for what I needed.