Spaces are important to separate words in a sentence making it easier to read. However, one space between two words will be enough and all other spaces will be extra spaces. The following formula is to figure out how many extra spaces are in a particular cell.
Example: The column A contains a list of sentences, and each includes a number of spaces. You are asked to count the number of extra spaces in each cell.
Formula: How many extra spaces are in cell A3?
= LEN(A3)-LEN(TRIM(A3))
Explanation:
- LEN(A3): To count the length of cell A3 and it returns 27;
- TRIM(A3): To remove extra spaces and leave one space between two words;
- LEN(TRIM(A3)): To count the length of the cell after removing extra spaces, and it returns 22;
- LEN(A3) – LEN(SUBSTITUTE(A3," ","")): The difference between the length before and after removing the extra space.