# 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:

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.