HomeGoogle DocsSpreadsheetHow to Use Trim Function with Split in Google Sheets

How to Use Trim Function with Split in Google Sheets

Published on

It’s important to know how to use the TRIM function with SPLIT in Google Sheets. If you use the SPLIT function, then you would probably understand the importance of it. I will come to that. Before that let us have to take a look at the functions involved here. So it will be beneficial for newbies too.

How to Use TRIM Formula in Google Sheets

I’m sure you know the purpose of the TRIM function in Google Sheets. We use this function to remove leading spaces, trailing spaces as well as any extra spaces in a text.

Tutorial: How to Remove Additional, Double or Extra Spaces in Google Sheets

How to Use SPLIT Formula in Google Sheets

Here again, you may also be familiar with the use of the SPLIT function in Google Sheets. As you may know, we can use Google Sheets Split function to split texts based on a given delimiter.

Tutorial: How to Split Text to Columns or Rows in Google Sheets by Delimiter

Importance of SPLIT and TRIM Combination in Google Sheets

Now we are back to the topic of our Tutorial. Before going to learn how to use Trim Function With Split in Google Sheets, you should know why we require it? See the below example.

I am going to split a long text which is in cell A1.

split causes leading spaces

The below is the formula that I’ve used for this in cell A5.

=split(A1,",")

See the Split result in Row 5. I’ve used the Comma as the delimiter to split the Text. So based on that, there are three texts after the split that are in the cells A5, B5, and C5.

Here the issue lies! You can see a leading space in the second and third texts (strings) that are in cell B5 and C5 respectively. What does it mean?

It simply means SPLIT in Google Sheets Causes Leading Spaces!

So how to remove such leading spaces after Split? You may think a normal Trim can remove the leading space. Nope! TRIM function alone can’t remove a leading space by using it with SPLIT.

So we can see how to properly use the TRIM function with SPLIT function in Google Sheets.

How to Use Trim Function with Split in Google Sheets

We can go ahead with the above same example. I will tell you how to remove those spaces marked on the image.

Don’t think a Trim function wrapped with the above Split formula can remove the leading spaces.

This formula may not work!

=trim(split(A1,","))

Below is the correct formula to remove the leading space in a Split text.

=ArrayFormula(trim(split(A1,",")))

Yup! Combine ArrayFormula, TRIM, and SPLIT to perfectly remove leading space. Try this formula and I hope you will get back to me with your valuable comments.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

13 COMMENTS

  1. Hi,

    I have numbers on different lines in a cell, for example, “1+line break+2+line break+3,” all in A1.

    I want to split 1, 2, and 3 into different rows (A2:A4)

    How do I do that?

  2. Hi Prashanth,

    Thank you for trying to resolve my problem, I do not wish to use multiple tables, as that would mean I would have to use a lot of queries and/or index functions for each stock code, which would cause the following issues:

    (1) I would have to create a lot more tabs, due to the 50 calls limitation,
    (2) It would slow down my pc due to the bulk calls.

    I will check with a google sheet forum to see if they can resolve this, otherwise, I will have to resort to the above method.

    • Hi, Ron,

      You may please post it on the official Google Sheets forum at “https://support.google.com/docs/threads?hl=en&thread_filter=(category:docs_sheets)” or on the StackOverflow at “https://stackoverflow.com/questions/tagged/google-sheets”.

      There you can hopefully find some awesome coders.

      • Hi Prashanth,

        Here is the URL and xPath: The url is “https://wallmine.com/asx/2be.ax”, and xPath is /html/body/main/section/div[4]/div[1]/div[2]. that I would like to display Headers in first row across columns, and associted data in second row across columns.

        Regards, Ron.

        • Hi, Ron,

          I’m not well familiar with XPath.

          Since you have already imported the data, and want to split it, I have checked the formula and output.

          It’s really tough to format the imported data as per the table in the source.

          So I recommend you to use multiple IMPORTHTML formulas to import the tables as demonstrated in Sheet2 in your shared file.

  3. Thanks for this, saved my life too! Can anyone explain why this works – why is ArrayFormula needed, and why can’t TRIM do this function alone?

LEAVE A REPLY

Please enter your comment!
Please enter your name here