How to Use Trim Function With Split in Google Sheets

0
69
How to Use Trim Function With Split in Google Sheets

It’s important to know how to use TRIM function with SPLIT in Google Sheets. If you use SPLIT function, then you would probably understand the importance of it. I will come to that. Before that let us have 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 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 SPLIT function in Google Sheets. As you know, we can use Google Sheets Split function to split texts based on 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 our Tutorial. Before going to learn how to use Trim Function With Split in Google Sheets, you should know why we require it?

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

split causes leading spacesThe below is the formula I’ve used for this in Cell A5.

Formula: =split(A1,”,”)

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

Here the issue lies. You can see a leading space in the second and third texts that is in Cell B5 and C5 respectively. What 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 when using with SPLIT.

So we can see how to properly use 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 that 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 get back to me with your valuable comments.

LEAVE A REPLY

Please enter your comment!
Please enter your name here