Excel: Count the Number of Words in a Cell

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!

Read More

PowerPoint: Supported Video File Formats

Tried to insert a video in PowerPoint and it didn't work? Maybe it was in a file format that isn't supported by PowerPoint. Here's a list of supported file formats:

Motion Picture Experts Group
.mpg
.mpeg
.m1v
.mp2
.mpa
.mpe

Microsoft streaming format
.asf
.asx

Microsoft Windows Media Video
.wmv

Audio Video Interleave
.avi

QuickTime (v1 or v2.x)
.mov
.qt

If you have a video in any other format, I'd suggest finding and using software to convert it to one that will work in PowerPoint.

Read More

OneNote: Can you print the ruler lines?

A fair amount of digging has revealed to me the unfortunate fact that no, OneNote does not include the option to print the ruler lines on your pages. The only workaround I can see is to actually insert an image of ruler lines on the page yourself, and put your content over it. That's kind of sloppy though, isn't it?

Maybe if we all write nice letters.

Dear Microsoft:

Please let us print ruler lines in the next version of OneNote. Some of us really like the way they look both on and off the screen.

Thanks,

Stacy

Read More