Title: How to Use Excel TRIM Function to Remove Spaces and Clean Data
Headings: How to Use Excel TRIM Function to Remove Spaces and Clean Data
How to Use Excel TRIM Function to Remove Spaces and Clean Data
Excel TRIM Function – An Overview
When to use Excel TRIM Function
What it Returns
Syntax
Input Arguments
Excel TRIM Function – Examples
Example 1 – Remove Leading, Trailing, and Double Spaces
Example 2 – Remove Leading Spaces Only
How does this formula work?
Example 3 – Correctly Count Number of words when there are Additional spaces
Related Excel Functions:

Content: For example, in the second address, 3 is in the third position (as there are two leading spaces before it). LEN (A1)-FIND (MID (TRIM (A1),1,1),A1)+1 – This part of the formula would give you the total length of the address after removing the leading spaces. =RIGHT (A1,LEN (A1)-FIND (MID (TRIM (A1),1,1),A1)+1) – This would give you the result where it extracts all the characters after the leading spaces. On similar lines, you can create formulas that can remove only the trailing, or only leading and trailing spaces from the text. Example 3 – Correctly Count Number of words when there are Additional spaces
Spaces in a text is also an indication of the word count in it. For example, if you want to count the total number of words in the phrase – “The quick brown fox jumps over the lazy dog”, you can simply count the spaces in between the words and add 1 to it. Here is the formula you can use to count words: =LEN (A1)-LEN (SUBSTITUTE (A1," ",""))+1
This formula substitutes all the spaces with blanks and then counts the number of spaces in the text and add 1 to it. However, when you have additional spaces in the text, this formula would not work. Here is the formula that you should use in such a case: