You can easily count the number of words in MS Word, but Excel doesn’t have a built-in tool for counting the number of words in a worksheet. In excel to count words we have to use Microsoft Visual Basics.

Steps to count words in a single cell

  • Press Alt+F11 together to open Microsoft Visual Basics
  • From there Go to Insert > Module

  • In the above opened window, paste/write the following VBA Code:-
    “Function intWordCount(rng As Range) As Integer
    intWordCount = UBound(Split(Application.WorksheetFunction.Trim(rng.Value), ‘ ‘), 1) + 1”

  • Press ALT+Q, to close the Microsoft VBA window.
  • Now to count text, we will use the following formula
    “=intwordcount(A1)”

Steps to count words of an Entire Range:

  • Press Alt+F11 together to open Microsoft Visual Basics
  • From there Go to Insert > Module
  • In the window write/ paste the following VBA code:-
    “Sub CountWords()

    Dim xRg As Range

    Dim xRgEach As Range

    Dim xAddress As String

    Dim xRgVal As String

    Dim xRgNum As Long

    Dim xNum As Long

    On Error Resume Next

    xAddress = ActiveWindow.RangeSelection.Address

    Set xRg = Application.InputBox(‘Please select a range:’, ‘Count Words’, xAddress, , , , , 8)

    If xRg Is Nothing Then Exit Sub

    Application.ScreenUpdating = False

    If Application.WorksheetFunction.CountBlank(xRg) = xRg.Count Then

        MsgBox ‘Words In Selection Is: 0’, vbInformation, ‘Count Words’

        Exit Sub

    End If

    For Each xRgEach In xRg

        xRgVal = xRgEach.Value

        xRgVal = Application.WorksheetFunction.Trim(xRgVal)

        If xRgEach.Value <> ” Then

            xNum = Len(xRgVal) – Len(Replace(xRgVal, ‘ ‘, ”)) + 1

            xRgNum = xRgNum + xNum

        End If

    Next xRgEach

    MsgBox ‘Words In Selection Is: ‘ & Format(xRgNum, ‘#,##0’), vbOKOnly, ‘Count Words’

    Application.ScreenUpdating = True

End Sub”

  • After writing/ pasting the whole code, Press F5 to run the code.
  • After pressing F5, such a pop up window will appear.

  • Define your range in such window.

  • Click on OK and total words in the entire range will be displayed.

How do I count text cells in Excel?

On the Formulas tab, click More Functions, point to Statistical, and then click one of the following functions:
  1. COUNTA: To count cells that are not empty.
  2. COUNT: To count cells that contain numbers.
  3. COUNTBLANK: To count cells that are blank.
  4. COUNTIF: To count cells that meets a specified criteria.

How do I count text and numbers in Excel?

Count number of cells with text with formula

Select a blank cell that you want the counting result showing in. 2. Copy and paste the formula =COUNTA(A1:D15)-COUNT(A1:D15) into the Formula Bar and then press the Enter key. Then you can see the total cell number showing in the selected cell.

How do I Countif text?

Count if cell contains text or part of text with the COUNTIF function
  1. =COUNTIF(B5:B10,”*”&D5&”*”)
  2. Syntax.
  3. =COUNTIF (range, criteria)
  4. Arguments.
  5. Notes:
  6. =COUNTIF(B5:B10,”*”)
  7. Tip. If you want to have a free trial (60-day) of this utility, please click to download it, and then go to apply the operation according above steps.

How do you Countif text?

If you want to learn how to count text in Excel, you need to use function COUNTIF with the criteria defined using wildcard *, with the formula: =COUNTIF(range;”*”) . Range is defined cell range where you want to count the text in Excel and wildcard * is criteria for all text occurrences in the defined range.

How do I count cells with specific text?

As below screenshot shows, we are now going to count cells with the text “Linda” in the cell range A2:A10. Select a blank cell for displaying the result. Then copy and paste the formula =COUNTIF($A$2:$A$10,”Linda”) into the Formula Bar, and press the Enter key on the keyboard.

How do I sum cells with specific text?

Using the SUMIF Function to Add Cells if They Contain Certain
  1. Formula for specific text: =SUMIF(range,”criterianame”,sum_range)
  2. Formula for partial text: =SUMIF(range,”*”&criteria&”*”,sum_range)
  3. Formula for text and numbers: =SUMIF(range,”*criterianame*”,sum_range)

Can you use Sumif with text?

For criteria, the SUMIF function allows using different data types including text, numbers, dates, cell references, logical operators (>, <, =, <>), wildcard characters (?, *, ~) and other functions.

Does Sumif work with text?

Text Criteria

Use the SUMIF function in Excel to sum cells based on text strings that meet specific criteria. Always enclose text in double quotation marks.

Is there a Sumif function for text?

SUMIF function is a built-in function in Excel categorized as a Mathematical/Trigonometric function. SUMIF text is useful in cases where we wish to sum numbers in a range of cells based on text criteria. SUMIF function is not case-sensitive.

How do I get Excel to ignore text in sum?

There is a formula can help you quickly sum up the column ignore #N/A. Select a blank cell, C3 for instance, and type this formula =SUMIF(A1:A14,”<>#N/A”), press Enter key to get the result.

How do I sum a column in Excel with words and numbers?

In the Choose a formula list box, click to select Sum based on the same text option; Then, in the Arguments input section, select the range of cells containing the text and numbers that you want to sum in the Range textbox, and then, select the text cell you want to sum values based on in the Text textbox.

How do I ignore letters in Excel?

Select a blank cell you will return the text string without letters, and enter the formula =StripChar(A2) (A2 is the cell that you will remove letters from) into it, and drag the Fill Handle down to the range as you need.

How do you write an IF THEN formula in Excel?

Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it’s false. For example: =IF(A2>B2,”Over Budget”,”OK”) =IF(A2=B2,B4-A4,””)

How do I use Isblank in Excel?

Use the ISBLANK function to test if a cell is empty or not. For example, =ISBLANK(A1) will return TRUE if A1 is empty, and FALSE if A1 contains text a formula (even if the formula returns an empty string “”).

What is the difference between isEmpty and isBlank?

isBlank() vs isEmpty()

The difference between both methods is that isEmpty() method returns true if, and only if, string length is 0. isBlank() method only checks for non-whitespace characters. It does not check the string length.

How do you check if range is blank in Excel?

The ISBLANK function in Excel checks whether a cell is blank or not. Like other IS functions, it always returns a Boolean value as the result: TRUE if a cell is empty and FALSE if a cell is not empty.