How To Count A Word In Excel

How to count the number of words in a prison cell or a range cells in Excel?

Yous can easily count the number of words in MS Discussion, simply Excel doesn't have a congenital-in tool for counting the number of words in a worksheet. However, you can count the number of words in Excel with post-obit methods:

Count number of words with formula
Count number of words in a single cell with User Divers Functions
Count number of words in specified range with VBA lawmaking
Easily count the number of words with Kutools for Excel (several clicks)

Count the number of words with formula

Here are two formulas for yous to count words in a single cell and in a range cells.

Count words in a single prison cell

Please enter this formula =IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+ane) into the Formula Bar, and so press the Enter key.

Note: In the formula, A2 is the cell yous will count number of words inside.

Yous can run across the result as below screenshot shown:

Count words in a range of cells with assortment formula

If you want to count the words in a range of cells, please enter formula =SUM(IF(LEN(TRIM(A2:A3))=0,0,LEN(TRIM(A2:A3))-LEN(SUBSTITUTE(A2:A3," ",""))+1)) into the formula bar, and and then press the Shift + Ctrl + Enter keys simultaneously to become the outcome. Come across screenshot:

Notation: A2:A3 is the range with words you volition count.

Count the number of words with User Defined Functions

Also, you tin count the words in a cell with the User Defined Functions, please do as follows:

1. Press Alt + F11 keys together to open the Microsoft Visual Basic for applications window.

ii. In the winodw, click Insert > Module, then copy and paste beneath VBA code into the Module. See screenshot:

VBA code: Count number of words in a jail cell.

                Function intWordCount(rng As Range) Every bit Integer 'Update past Extendoffice 2018/iii/7     intWordCount = UBound(Split(Application.WorksheetFunction.Trim(rng.Value), " "), 1) + one End Function

two. Printing the Alt + Q keys to close the Microsoft Visual Basic for Applications winodw. Select a blank cell in your worksheet, enter formula "=intwordcount(A2)" into the Formula Bar, so printing the Enter key to become the result. See screenshot:

Note: In the formula, A2 is the jail cell you will count number of words inside.

If you want to count number of words in a sure range, please apply the following method.

Count number of words in specified range with VBA lawmaking

The post-obit VBA code tin can help you quickly count number of words in a specified range.

1. Press Alt + F11 keys together to open the Microsoft Visual Basic for applications window.

2. In the winodw, click Insert > Module, and then copy and paste below VBA code into the Module. Run into screenshot:

VBA lawmaking: Count number of words in selected range.

                Sub CountWords()     Dim xRg Equally Range     Dim xRgEach As Range     Dim xAddress Every bit String     Dim xRgVal As Cord     Dim xRgNum As Long     Dim xNum Equally Long     On Error Resume Next     xAddress = ActiveWindow.RangeSelection.Address     Prepare xRg = Application.InputBox("Please select a range:", "Kutools For Excel", xAddress, , , , , eight)     If xRg Is Nothing Then Leave Sub     Application.ScreenUpdating = False     If Application.WorksheetFunction.CountBlank(xRg) = xRg.Count Then         MsgBox "Words In Option Is: 0", vbInformation, "Kutools For Excel"         Leave Sub     End If     For Each xRgEach In xRg         xRgVal = xRgEach.Value         xRgVal = Awarding.WorksheetFunction.Trim(xRgVal)         If xRgEach.Value <> "" Then             xNum = Len(xRgVal) - Len(Replace(xRgVal, " ", "")) + i             xRgNum = xRgNum + xNum         Stop If     Next xRgEach     MsgBox "Words In Choice Is: " & Format(xRgNum, "#,##0"), vbOKOnly, "Kutools For Excel"     Awarding.ScreenUpdating = True Cease Sub

3. Press the F5 key to run the lawmaking. In the popping upwardly Kutools for Excel dialog box, please select the range yous will count number of words inside, and then click the OK push button. Run across screenshot:

Then another Kutools for Excel dialog box pops up to show you the total number of words in seleted range. See screenshot:

Count the number of words with Kutools for Excel (only clicks)

Y ou tin try the Count words in range utility of Kutools for Excel to hands count number of words in a cell or a range with merely several clicks.

Before applying Kutools for Excel, please download and install it firstly.

1. Select a cell for returning the result, so click Kutools > Formula Helper > Formula Helper.

2. In the Formulas Helper dialog box, delight configure as follows.

In the Choose a formula box, select Count total words;
Tips: You can check the Filter box, enter a key word to apace filter the formula equally you need.
In the Range box, specify the prison cell or range in which yous want to count full words;
Click the OK button. Encounter screenshot:

And so you will get the number of words in a specified jail cell or range.

If y'all want to have a free trial ( thirty-day) of this utility, please click to download it, and then go to apply the operation co-ordinate to a higher place steps.

Easily count the number of words with Kutools for Excel

Kutools for Excel includes 300+ powerful features for Microsoft Excel. Costless to try with no limitation in 30 days. Download now!

The All-time Part Productivity Tools

Kutools for Excel Solves Nearly of Your Problems, and Increases Your Productivity past 80%

Reuse: Quickly insert complex formulas, charts and anything that yous have used before; Encrypt Cells with password; Create Mailing List and transport emails...
Super Formula Bar (hands edit multiple lines of text and formula); Reading Layout (hands read and edit big numbers of cells); Paste to Filtered Range...
Merge Cells/Rows/Columns without losing Data; Divide Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Notice in Many Workbooks; Random Select...
Exact Re-create Multiple Cells without changing formula reference; Car Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Excerpt Text, Add Text, Remove by Position, Remove Space; Create and Impress Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (salvage and employ filter schemes to other sheets); Advanced Sort past calendar month/calendar week/day, frequency and more; Special Filter past bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on cardinal columns; Split Data into Multiple Sheets; Batch Catechumen xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-twenty-four hours free trial. 60-twenty-four hours money back guarantee.

kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

Enable tabbed editing and reading in Word, Excel, PowerPoint , Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the aforementioned window, rather than in new windows.
Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

officetab bottom