Count number of spaces in a string
This tutorial shows how to count the number of spaces in a string using an Excel formula, with the LEN and SUBSTITUTE functions
Hard coded formula
Cell reference formula
|
=LEN("Using excel")-LEN(SUBSTITUTE("Using excel"," ",""))
=LEN(B5)-LEN(SUBSTITUTE(B5," ",""))
|
GENERIC FORMULA
=LEN(string)-LEN(SUBSTITUTE(string," ",""))
ARGUMENTS GENERIC FORMULA
=LEN(string)-LEN(SUBSTITUTE(string," ",""))
ARGUMENTS EXPLANATION This formula uses the SUBSTITUTE function to remove the spaces from a string and then applies the LEN function to count the number of character from the string without space. The LEN function is also used to calculate the number of characters from a string which includes spaces. The formula then subtracts the number of characters derived from a string that excludes spaces from number of characters derived from a string that includes spaces.
Click on either the Hard Coded or Cell Reference button to view the formula that has the string from which to count the number of spaces directly entered into the formula or referenced to a range of cells. |
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count number of characters in a cell excluding spaces | How to count the total number of characters in a cell, excluding spaces | |
| Count number of characters in a range excluding spaces | How to count total number of characters in a range, excluding spaces |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| SUBSTITUTE Function | The Excel SUBSTITUTE function replaces characters with another in a specified string | |
| LEN Function | The Excel LEN function returns the number of characters in a specified string |