Yet another great question came to me by e-mail this evening: "how do I count the number of words in a given cell?".
You can get this answer using a formula! Here it is (please substitute 'A1' with the cell reference of your choosing):
=LEN(TRIM(A1))-LEN(SUBSTITUTE((A1)," ",""))+1
And for those of you that are curious as to what exactly this formula is doing:
LEN returns the number of characters.
TRIM removes extra spaces at the beginning or end of the string.
SUBSTITUTE replaces the character of your choosing with something else. In this case it's replacing spaces (" ") with nothing ("").
So, this formula is taking the number of characters in cell A1 (disregarding any extra spaces at the beginning or end), and subtracting from it the number of characters in cell A1 if it had no spaces. Add one and you get the number of words.
Wait, what?
You're right, I'm not very good at explaining this one. So I'm going to show you an example:
If cell A1 contains "Stacy DuBois", the formula is taking 12 (how many characters there are) minus 11 (how many characters there are without the space in between my first and last name). That gives you 1. Add 1 and you get 2. That's how many words are in that cell. This will work with any number of words in a cell. Ah, the wonders of math!
You can get this answer using a formula! Here it is (please substitute 'A1' with the cell reference of your choosing):
=LEN(TRIM(A1))-LEN(SUBSTITUTE((A1)," ",""))+1
And for those of you that are curious as to what exactly this formula is doing:
LEN returns the number of characters.
TRIM removes extra spaces at the beginning or end of the string.
SUBSTITUTE replaces the character of your choosing with something else. In this case it's replacing spaces (" ") with nothing ("").
So, this formula is taking the number of characters in cell A1 (disregarding any extra spaces at the beginning or end), and subtracting from it the number of characters in cell A1 if it had no spaces. Add one and you get the number of words.
Wait, what?
You're right, I'm not very good at explaining this one. So I'm going to show you an example:
If cell A1 contains "Stacy DuBois", the formula is taking 12 (how many characters there are) minus 11 (how many characters there are without the space in between my first and last name). That gives you 1. Add 1 and you get 2. That's how many words are in that cell. This will work with any number of words in a cell. Ah, the wonders of math!
Posts
Thank you, this was excellent, and just what I was looking for!!
ReplyDelete