To figure out how many spaces in a particular cell, you can use the following formula:
Formula:
= LEN(Cell)-LEN(SUBSTITUTE(Cell," ",""))
Explanations:
– LEN(Cell): to count the length of the content in the cell;
– SUBSTITUTE(Cell," ",""): to remove spaces in the cell using substitute function;
– LEN(SUBSTITUTE(Cell," ","")): to count the length of content without spaces;
– LEN(Cell) – LEN(SUBSTITUTE(Cell," ","")): to count the length with spaces minus the length without spaces.
Example: To count how many spaces in cell A1
– LEN(A1): to count the length of cell A1, which is 32;
– SUBSTITUTE(A1," ",""): to remove spaces in the cell;
– LEN(SUBSTITUTE(A1," ","")): to count the length of the content without spaces, which is 26;
– LEN(A1)-LEN(SUBSTITUTE(A1," ","")): to count the length with spaces minus content length without spaces (32 – 26=6).
= LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
= LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
There are 6 spaces in cell A1.
