How to Count Number of Spaces in a Cell

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.

Count Number of Spaces in a Cell

Leave a Reply