# How to: Word counts in Excel

0
2913  Ever needed to know the word count of text in a certain cell in Excel? It’s not something that you would need know often, but now and again, it’s a useful thing to know how to do. Here’s a scenario:

You have a spreadsheet of product data ready to upload to an e-commerce site. Some of the data needs to be a specific length in order to fulfill criteria of the website. You need to identify the entries that are too long, in order to correct the data before loading it – but how do you do it?!

If you want to know the number of characters in the cell, for example if you are loading posts to twitter that have to be below 146 characters, then that’s fairly simple by using the =Len() formula, but there isn’t a specific function in Excel for counting the number of words. Because Excel is so clever, you can, however, nest a couple of different formulae inside each other, that would work it out for you. Here’s how:

## How to do it

Firstly, add an extra column in your spreadsheet for the results of the word count. Ideally this will be at the end of your spreadsheet so that you can hide or delete later if you need to use the database for other purposes, such as uploading to a website.

Next assuming that you want to count the number of words in cell “A2″, copy and paste this formula into the cell where you want to display you results (obviously good practise would have this formula in a different column but same row).

=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2,” “,””))+1) Press return and you’ll see you word count displayed. This example is counting the number of words in cell A2. To use this formula to count the number of words in a different cell, simply replace all instances of “A2″ with your chosen cell reference. So if your text was in cell C4, your formula would look like this.

=IF(LEN(TRIM(C4))=0,0,LEN(TRIM(C4))-LEN(SUBSTITUTE(C4,” “,””))+1)

Once you have set the formula in your first cell, it would be easy to then apply this formula to subsequent rows by simply copying the formula down. As in the example below, simply select the cell containing the formula, hover over the bottom right hand corner of the cell until you see the black cross hair then click and drag down to fill the cells below, your formula should autofill as you go to analyse the relevant cell. Going back to our initial scenario, we now want to identify the cells containing too many words, and we can do this using conditional formatting, or filtering your word count column using a ‘number filter’. 