Cell word count in Excel

Most of my assessments for teaching are turned in online, using Moodle. Some of them are turned in using online forums, which I then extract in an Excel sheet for marking, and for record keeping.

When extracted, each answer for each question is extracted into one Excel cell. I find that works out well for marking, and for storage. However, when it comes to giving a student feedback on how well they have done - or not - sometimes it is useful to get a word count.

I can't get that information on Moodle, and I couldn't work out how to do it in Excel. For a long while I would copy the contents of the cell into Word, and do a word count there. However, I figured that I SHOULD be able to do that in Excel and eventually found some time to go and look.

And I found this little piece of kit that does just that: it counts the words in a single Excel cell:

=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1)

So easy (!) when you know how :-)


Sam

• Reference: Extend Office (16 July 2016). How To Count The Number Of Words In A Cell Or A Range Cells In Excel? https://www.extendoffice.com/documents/excel/933-excel-count-words.html