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).
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.
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’.
Notes about this formula:
This formula works by nesting a few formulae inside each other. The TRIM formula trims all spaces from the the string of text, except spaces between words. A LEN formula counts the number of characters in the string. The formula overall basically subtracts the length of the string of text minus spaces from the length of the string of text including spaces, in order to identify the number of ‘spaces’. Working on the assumption that there is only one space between each word and no space at the beginning of the sentence, we can therefore calculate that the word count is equal to the number of spaces +1.
The exceptions: as you can see from the explanation above, there are a couple of assumptions made by the formula; that there is one space between every word and there are no extra spaces at the start or end of the string of text. If this is the case, your word count will be inaccurate. Similarly, if you have a cell with line breaks in it (e.g. you have used ALT + RETURN to force text onto a new line within a cell) where you won’t necessarily have added a space before the new line, the formula will not recognise the space and therefore not recognise a new word.