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