Table of Contents
To count the number of spaces in a cell using Excel, you can use the LEN function to get the total number of characters in the cell and then subtract the number of characters without spaces using the SUBSTITUTE function. This will give you the number of spaces in the cell. Alternatively, you can use the FIND function to search for a specific space character within the cell and then use the COUNTIF function to count the number of occurrences. Both methods will provide an accurate count of the number of spaces in the cell.
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.
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: