Learning the basic functions in Excel gives you more to work with and allows you to build more sophisticated formulas. Counting words could be your final goal or the result could be an input for another one of your Excel formulas. Though Excel has no function meant to count words, with a little bit of mixing and combining you can use basic Excel functions to get the word count in your Datasheet.
Kutools excel combine number of 2 cells how to#
Related: How to Create Professional Looking Excel Spreadsheets Get a Count of Your Words
Kutools excel combine number of 2 cells full#
Lastly, to prevent the formula from counting the spaces in a cell full of spaces only, you can use an IF statement that returns a zero for cells that don't have anything but spaces in them. This will basically remove the spaces from a cell. To do this, you can substitute the space character with a blank with the SUBSTITUTE function. In order to count the characters in a cell and exclude the space character, you can simply remove the spaces from the cell and then count them. Then, you'll have the number of spaces in a cell! 3. Simply count all the characters (including spaces) in a cell, then count all the characters excluding spaces, and subtract the latter from the former. However, getting the number of spaces in a cell is still possible with the LEN function. Related: Microsoft Excel Formulas That Are Extremely Useful Spaces are counted with the LEN function, however, so are all other characters. However, since counting words is to count the spaces, you need to somehow count the spaces. Counting the Spaces and the LettersĬounting the characters in a range is possible with the LEN function. If you want to learn more about the TRIM function and see it in action, read our article on how to use the TRIM function in Excel. This gives the formula a neat sentence to work with and prevents the formula from counting the extra spaces as words.
![kutools excel combine number of 2 cells kutools excel combine number of 2 cells](https://cdn.extendoffice.com/images/stories/shot-kutools-excel/kutools-excel-6.00/shot-kutools-excel-03.png)
The TRIM function basically removes all extra spaces in a cell and keeps only the single space between words. One useful function here will be the TRIM function. The first thing we need to get out of the way is cells that contain nothing or only contain spaces.
![kutools excel combine number of 2 cells kutools excel combine number of 2 cells](https://geekexcel.com/wp-content/uploads/2020/11/2-38.png)
Let's see what each part of the formula actually does. The formula below will do the word count for you. The formula is going to be a compound formula utilizing several basic Excel functions. Luckily, using the basic Excel functions and combining them into a compound formula allows you to achieve this.
![kutools excel combine number of 2 cells kutools excel combine number of 2 cells](https://tipsmake.com/data1/images/use-300-powerful-features-in-excel-with-the-kutools-utility-picture-8-RMfHtxJ8U.png)
![kutools excel combine number of 2 cells kutools excel combine number of 2 cells](https://img.informer.com/pb/kutools-for-excel-v25-main-window-outlook.png)
With this in mind, in order to count the words in Excel, you have to use a formula that accounts for all the possible holes. In that case, the word count result should be zero though counting the spaces will yield another, higher, result. For instance, if you have typed multiple spaces between words, then all of those spaces will be mistakenly counted as words, resulting in more words than there are.Īnother hole would be when a cell contains nothing but space or spaces. Related: Microsoft Excel Autofill Tricks to Build Spreadsheets Faster What Should the Formula Consider? Zero spaces, summed with one, will tell you that you have one word in your cell. If you have a single word in your cell, you won't have any spaces. To calculate the number of words, you'll have to count the spaces and sum the result with one. Therefore, if you have four spaces in a sentence, you have five words. When you're talking about counting words in a text, you are, in fact, trying to count the spaces between the words. The sum count of characters in the three cells will now appear in cell E2.Select cell E2 and in the formula bar, enter the formula below: =SUM(LEN(A1),LEN(A2),LEN(A3)) This will count the characters for the three cells separately and then sum them.