Count number of spaces in a range

This tutorial shows how to count the number of spaces in a range using an Excel formula, with the SUMPRODUCT, LEN and SUBSTITUTE functions

EXCEL FORMULA 1. Count number of spaces in a range

EXCEL

Count number of spaces in a range

=SUMPRODUCT(LEN(B5:B7)-LEN(SUBSTITUTE(B5:B7," ","")))
GENERIC FORMULA

=SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(range," ","")))

ARGUMENTS
range: A range from which to count the number of spaces.

EXPLANATION
This formula uses the SUBSTITUTE function to remove the spaces from each cell in a range and then applies the LEN function to count the number of character from each cell in a range without space. The LEN function is also used to calculate the number of characters from each cell in a range which includes spaces. The formula then subtracts the number of characters derived from each cell in a range that excludes spaces from number of characters derived from each cell in a range that includes spaces. The SUMPRODUCT is used to sum the number of spaces in each cell from the specified range.

RELATED TOPICS

Related Topic Description Related Topic and Description
Count number of spaces in a string How to count the number of spaces in a string
Count number of line breaks in a cell How to count the number of line breaks in a cell

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
SUMPRODUCT Function The Excel SUMPRODUCT function multiplies corresponding ranges and returns the sum of these values