Count the Number of Names in Cells using TEXT Functions

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

 

 

 

 

Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *