How to Count Words in Excel | Using VBA Function to Count Words in Excel

Показать описание

In this video, we’ll be showing you how to count words in Excel.

There is no built-in method of counting words in Excel so we’ll have to come up with our own solutions. If you want to know how many words are in single cell, range of cells, or the entire spreadsheet you’re going to need to use Formulas or VBA code.

We will go through the steps to count words by cell, range of cells, and the entire spreadsheet (VBA).

1. To count words in a single cell, use this formula in another cell and change B2 to whatever cell you want to count words in:
=IF(LEN(TRIM(B2))=0,0,LEN(TRIM(B2))-LEN(SUBSTITUTE(B2," ",""))+1)
2. If you want to count the cells in a range instead, use this formula and change B2:B8 to your range of cells and (IMPORTANT!) use Ctrl+Shift+Enter after you finish typing it to change it to use an array, you should see curly brackets form around the formula after pressing Ctrl+Shift+Enter:
=SUM(IF(LEN(TRIM(B2:B8))=0,0,LEN(TRIM(B2:B8))-LEN(SUBSTITUTE(B2:B8," ",""))+1))
3. Finally, follow the steps below if you would like to count the words in the entire spreadsheet.
a. Enable the Developer tab first. Go to File in the top-left and click Options.
b. Click Customize Ribbon on the left bar and then find Developer on the right and check the box next to it then click OK.
c. Go under the Developer tab that should appear at the top and click Visual Basic.
d. A new window should open. Click Insert at the top and then click Module.

Counting words in Excel is not easy without a built-in way to do it, but it is possible through formulas and VBA!

❓💬 What Excel features do you want to know how to perform? Let us know in the comments below.

#HowTech #Excel
--------------------------------------------------------------------------------------------------------------