You can use the following formula to count the number of spaces in a cell in Excel:
=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))
This particular formula counts the number of spaces in the string in cell A2.
The following example shows how to use this formula in practice.
Example: How to Count Number of Spaces in a Cell in Excel
Suppose we have the following column of strings in Excel:
Suppose that we would like to count the number of spaces in each cell.
We can type the following formula into cell B2 to do so:
=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))
We can then drag and fill this formula down to each remaining cell in column B:
Column B shows the number of spaces in each corresponding cell in column A.
For example:
- “Hello everyone” contains 1 space.
- “What is going on” contains 3 spaces.
- “This is a great day” contains 4 spaces.
And so on.
Note that if there are multiple spaces next to each other in a cell, then this formula will count each of those spaces individually.
For example, suppose cell A2 contains three spaces in between the words “Hello” and “everyone”:
Notice that the formula returns a value of 3 since there are three spaces between the two words.
To remove these extra spaces before counting them, we can use the following formula instead:
=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))
The following screenshot shows how to use this formula in practice:
We can see that the formula returns a value of 1 so it doesn’t count the extra spaces in the string multiple times.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Count If Cells Contain Text
Excel: How to Use COUNTIF with Multiple Ranges
Excel: How to Count Unique Values Based on Multiple Criteria
