Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to write a formula to count the number of names in cell. Let’s get started
SAMPLE DATA
In the screenshot below, we have a single column that contains different length of names. Our goal is to use formula to count the number of names in each cells.
Count Number of Names in each Cells
To perform this operation, we are going to use combination of text functions such as LEN, TRIM and SUBSTITUTE functions.
- In cell 12, execute the formula: =LEN(TRIM(H2))-LEN(SUBSTITUTE(TRIM(H2),” “,””))+1
- Click Enter and Copy down the formula using the fill handle.
The LEN(TRIM(H2)) part of the formula returned the length of the characters in cell H2 which also included the space in between the name while -LEN(SUBSTITUTE(TRIM(H2),” “,””))+1 of the formula returned the length of the characters such that the space which is considered as the old text within the SUBSTITUENT function is replaced with double quote and the we added plus one to return the accurate count of the names in the cells
See you in the next tutorial