Remove leading spaces in Google Sheets using the TRIM function.
Syntax=TRIM(text)
text – the text or the cell containing the text to be trimmed.
Sample Usage=TRIM(" Peter James")
//Removes the initial space from the text.=TRIM(B6)
//Removes the leading spaces from the text in cell B6
Leading spaces in texts can be undesirable at times. Also, working with texts in Google Sheets often requires performing a character or letter count for different reasons.
The LEN function which is Google Sheets’s primary way of counting characters would return values that represent the number of letters plus the spaces.
For instance, let’s assume we want to count the number of letters in the example below.
From the answers, you can see that the LEN function includes the leading spaces as characters.
While this isn’t the only reason to want to remove leading spaces in texts, it does well to illustrate how eliminating them could prove helpful.
In this guide, we will learn how to easily remove leading spaces in your text and we will consider two different options that do this effectively.
How to remove leading spaces in Google Sheets
Removing leading spaces in Google Sheets is simple, let us consider various examples and different techniques in each.
Example 1: Using the TRIM Whitespaces feature in Google Sheets
This is a much easier way to remove leading spaces in Google Sheets than other alternatives, you can utilize it quickly. It works similarly to the TRIM function by removing leading spaces in texts, as well as trailing spaces. For those who do not want to use a formula, this is an ideal approach.
Assuming we have our dataset shown in the previous table.
We can easily remove the leading spaces by following these steps:
- Highlight/select the data
- Go to Data ➡ Data cleanup ➡ Trim whitespace
The Trim Whitespace before performing its operation will show a dialog indicating the number of cells you are about to TRIM. Press OK to accept this and the dialog will disappear
The Trim Whitespace feature successfully removed the leading spaces in our texts as we can see below.
Example 2: Remove leading spaces in Google Sheets using the TRIM function.
Google Sheets has a formula dedicated to just removing spaces – both leading and trailing. It is called the TRIM function. As the name suggests, the TRIM function cuts out the leading and trailing spaces of given text input. And below is its syntax:
=TRIM(text)Where text is the text you want the function to trim.
Back to our example, let us provide the texts in cell B6.
- Begin to type =TRIM
- Google will auto-suggest functions
- Select the TRIM function
- Enter cell B6
The TRIM function returns text with no leading spaces in C6.
Note: unlike the Trim Whitespace feature that directly removes the whitespaces in the cells containing the text, the TRIM function will return the text in another cell with the whitespace removed.
Conclusion
It is easy to remove leading space in Google Sheets with the inbuilt TRIM feature and TRIM function. Also, add-ons provide a more sophisticated way to do this.
FAQs
How do I get rid of leading spaces in Google Sheets?
To get rid of leading spaces you can either use the Trim Whitespace feature or you use the TRIM function, these are the easiest ways to remove whitespace.
For Trim Whitespace :
- Select the cells.
- Go to Data ➡ Data cleanup ➡ Trim whitespace
For the TRIM Function, simply provide the text as a parameter.
How do you get rid of leading and trailing spaces?
The same approach used in leading spaces applies to the trailing spaces, the Trim Whitespace feature, the TRIM function, and the Power Tools add-on all remove trailing spaces in Google sheets.
See also
You might also like other similar articles in your blog, check them out.
Separate the first and last names in Google Sheets