Spaces are important to separate words in a sentence making it easier to read. If you need to figure out the cells that are spaces, please check here. The following formula is to figure out how many spaces are in a particular cell.
Example: The text "Count the number of spaces in a cell" is in cell A1, and you need to figure out how many spaces in the sentence.
Formula: To count how many spaces in cell A1
= LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
- LEN(A1) is to count the length of cell A1, both letters and numbers, which is 32;
- SUBSTITUTE(A1," ","") is to remove spaces in the cell, and only leave the contents;
- LEN(SUBSTITUTE(A1," ","")) is to count the length of the content without spaces, which is 26;
- LEN(A1)-LEN(SUBSTITUTE(A1," ","")) is to count the number of spaces in the cell, which is the length with spaces minus the content length without spaces (32 – 26=6).