Word Count with Double Spaces

I need a word count where anything over 280 words is flagged. I am copying the words from the online exam and pasting into excel but the formatting is disrupted and there are extra spaces between lines and before sentences, as well as extra characters that did not translate (the cross and the bar). If you can help me get a more accurate word count by editing the formula that I have, that would be great.

When I paste the words into MS word I get a different word count (often much lower, ~10 words). I inserted a comments in some cells (C3, C15) with what the word count was in MS Word.

In C3 you could change your formula to this:

=LEN(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,".",""),",",""),"-",""),"+",""),"/",""),"\",""),CHAR(10),"")))-LEN(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,".",""),",",""),"-",""),"+",""),"/",""),"\","")," ",""),CHAR(10),"")))+1

which will then agree with the word count in Word. Note that you had " " in most of your SUBSTITUTE functions, and your TRIM function was not acting on the sentences after making all the changes. Also, you had not accounted for the line-feed character (CHAR(10) ).

Hope this helps.

Pete

Thank you! I checked and it looks like it is working perfectly now.

Glad to help - thanks for the rep.

Pete