Excel: How to Count Number of Spaces in a 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:

Excel count spaces in cell

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

Leave a Reply

Your email address will not be published. Required fields are marked *