Say I have the text, "There was no fire alarm. There were no sprinklers or fire escapes. And when a band member tried to put out a fire that had been started by pyrotechnics, the extinguisher didn't work." in cell A1 and need to calculate the average sentence length for that cell. I tried =AVERAGE(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1) but it returned "33", not 11.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Average sentence length in a string
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 73,971
- Office Version
- 365
- Platform
- Windows
Welcome to the Board!
I am not sure what you think the "AVERAGE" function is doing here. All you are doing is counting the number of spaces. But you are not counting or dividing by the number of sentences.
You would need to count the periods and divide by them, i.e.
Note that this works for your example, but if you have other punctuation other than periods (like ! or ?), you would need to count those too.
I am not sure what you think the "AVERAGE" function is doing here. All you are doing is counting the number of spaces. But you are not counting or dividing by the number of sentences.
You would need to count the periods and divide by them, i.e.
Code:
=(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)/(LEN(A2)-LEN(SUBSTITUTE(A2,".","")))
Upvote
0
Similar threads
- Question
- Question
