Count the number of words separated by comma in Excel
While working on a Project somewhere I needed to count the number of words in a particular cell. In this tutorial we’ll be learning to Count the number of words in a cell in Excel. The tutorial explained in step by step and worksheet is attached.
I have used two Excel functions LEN() and SUBSTITUTE() for the calculations.
Step 1. Calculate the length
In this step first we have to calculate the number characters in a cell using LEN() function. It count and returns the number of characters in a cell including the spaces,commas and other characters.
=LEN(Cell Number)
Step 2. Removing the Special characters, punctuation marks and spaces
To count the number of words we have to remove the commas in our case using SUBSTITUE() function. It replaces the words or character with the desired value in a string.
=SUBSTITUTE("OriginalText","TextToRemove","TextToReplacewith")
Here I am replacing comma with none so all the commas are removed as you can see in cell E11.
Step 3. Calculating the new length
Now we have to calculate the length/number of character in the string removing the commas
Step 4. Counting the words
This is the final step to calculate the number of words. So the calculation goes like this
Length of original string- Length of substituted string string+1
=1+E10-E12
here +1 because the comma ends before the last word.
Note- The calculation can be done in just one cell, I have shown in different cells just to explain the steps and working of the functions. Following formula is used for single cell calculation.
=LEN(E4)-LEN(SUBSTITUTE(E4,",",""))+1
The same logic can be applied if words are separated by space or any other character, you just have to remove all the other(,!# etc.) characters.
This is my first time visit at here and i am truly pleassant to read all at single place.
I’m amazed, I have to admit. Rarely do I come across a blog that’s both equally educative and interesting,
and without a doubt, you’ve hit the nail on the head.
The issue is an issue that too few people are speaking intelligently about.
I am very happy that I found this during my search for something
concerning this.
Great post.
Heya i’m for the first time here. I came across this board and I find It really helpful & it helped me
out much. I hope to provide one thing back and help others like
you helped me.
Very good post. I absolutely love this website. Continue the good
work!
Every weekend i used to pay a visit this website, for the reason that i wish for enjoyment, since this this site conations actually
fastidious funny stuff too.
If you are counting words based on “,” symbol, what about to use thuis formula ?=COUNTCHAR(E4,”,”)+1 ….. you can also replace it with any others like space and other….
hmm ?
Is there any function like that?
Just use ” =COUNTCHAR(E4,”,”)+1 ” and thats it 🙂