Cập nhật thông tin chi tiết về How To Get The Word Count In Excel (Using Simple Formulas) mới nhất trên website Trucbachconcert.com. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất.
Want to get the word count in Excel? Believe it or not, Excel does not have an inbuilt word counter.
But don’t worry.
A cool bunch of excel functions (or a little bit of VBA if you’re feeling fancy) can easily do this for you.
In this tutorial, I will show a couple of ways to count words in Excel using simple formulas. And at the end, will also cover a technique to create a custom formula using VBA that will quickly give you the word count of any text in any cell.
Formula to Get Word Count in Excel
Before I give you the exact formula, let’s quickly cover the logic to get the word count.
Suppose I have a sentence as shown below for which I want to get the word count.
While Excel cannot count the number of words, it can count the number of spaces in a sentence.
So to get the word count, we can count these spaces instead of words and add 1 to the total (as the number of space would be one less the number of words).
Now there can be two possibilities:
There is a single space between each word
There are multiple spaces between words.
So let’s see how to count the total number of words in each case.
Example 1 – When there is a single space between words
Let’s say I have the following text in cell A1: Let the cat out of the bag
To count the number of words, here is the formula I would use:
This would return ‘7’ as a result.
Here is how this formula works:
LEN(A1) – This part of the formula returns 26, which is the total number of characters in the text in cell A1. It includes the text characters as well as the space characters.
SUBSTITUTE(A1,” “,””) – This part of the formula removes all the spaces from the text. So the result, in this case, would be Letthecatoutofthebag.
LEN(SUBSTITUTE(A1,” “,“”) – This part of the formula counts the total number of characters in the text that has no spaces. So the result of this would be 20.
LEN(A1)-LEN(SUBSTITUTE(A1,” “,“”)) – This would subtract the text length without spaces from the text length with spaces. In the above example, it would be 26-20 which is 6.
=LEN(A1)-LEN(SUBSTITUTE(A1,” “,“”))+1 – We add 1 to the overall result as the total number of spaces is one less than the total number of words. For example, there is one space in two words and two spaces in three words.
Now, this works well if you have only one space character between words. But it wouldn’t work if you have more than one space in between words.
In that case, use the formula in the next example.
Example 2: When there are multiple spaces between words
Let’s say you have the following text: Let the cat out of the bag
In this case, there are multiple space characters between words.
To get the word count, we first need to remove all the extra spaces (such that there is only one space character between two words) and then count the total number of spaces.
Here is the formula that will give us the right number of words:
This is a similar formula used in the above example, with a slight change – we have also used the TRIM function here.
Excel TRIM function removes any leading, trailing, and extra spaces (except single spaces between words).
The rest of the formula works the same (as explained in Example 1).
Note: If there are no spaces between words, it is considered as one word.
Using VBA Custom Function to Count Words in Excel
While the above formulas work great, if you have a need to calculate the word count often, you can use VBA to create a custom function (also called a User Defined Function).
The benefit of using a custom function is that you can create it once and then use it like any other regular Excel function. So instead of creating a long complex formula as we did in the two examples above, you have a simple formula that takes the cell reference and instantly gives you the word count.
Here is the code that will create this custom function to get the word count in Excel.Function WordCount(CellRef As Range) Dim TextStrng As String Dim Result() As String Result = Split(WorksheetFunction.Trim(CellRef.Text), " ") WordCount = UBound(Result()) + 1
How to Count Total Number of Words in a Cell of Excel?
The steps to count the total number of words in a cell of Excel are listed as follows:
Step 1: Select the cell in the Excel sheet where you want the result to appear.
Step 2: For counting the number of words in cell A1, enter the formula shown in the following image.
To count the number of words in a range of cells, apply the equation that counts the words in a cell and implant it either inside the SUM or the SUMPRODUCT function.
The formula to count words of a particular range is “=LEN(TRIM(cell))- LEN(SUBSTITUTE(cell,” “,””))+1.”
Step 1: Select the range of data whose words you wish to count.
Step 2: Enter the formula in the cell where you want the result to display as shown in the succeeding image.
Step 4: Drag the fill handle to all cells to get the word count of each cell.
To count the number of times a specific word appears in a range of cells, we utilize a comparative methodology. We count the explicit words in a cell and consolidate it with the SUM or SUMPRODUCT function.
Step 1: Select the cell and enter the formula “=(LEN(cell)-LEN(SUBSTITUTE(cell,word,””)))/LEN(word)” as shown in the following image.
The result in cell A14 is 4.
The formula for counting the number of words in Excel is:
Let us understand the working of this formula.
To begin with, we utilize the SUBSTITUTE function to evacuate and displace all spaces in the cell with a vacant content string (“). The LEN function restores the length of the string without spaces.
Popular Course in this category
Further, we utilize the TRIM function to remove extra spaces in a cell. A worksheet may contain a lot of imperceptible spaces. Such coincidental occurrence might be towards the start or end of the text (leading and trailing spaces). Since extra spaces return an incorrect word count, the TRIM function is used before computing the length of the string.
Characteristics of the Word Count Formula
The features of the word count formula are listed as follows:
It is not an in-built formula of Excel and needs to be entered manually.
It is not case sensitive which implies that any type of alphabetical letters can be used.
It is essential to place the $ sign to fix the cell reference while copying the formula.
It works well if the cell, for which the formula is being used, is checked beforehand.
It requires the correct range to be specified at the time of usage.
Frequently Asked Questions
#1 – How to count the number of times a single character appears in a cell?
The formula to count the occurrence of a single character in a cell is stated as follows:
The “cell_ref” stands for cell reference. The letter “a” stands for the character that the user wants to count.
#2 – How to count the number of times a single character appears in a range of cells?
The formula to count the occurrence of a single character in a range of cells is stated as follows:
The “range” stands for the range of cells to which the formula is applied. The letter “a” stands for the character that the user wants to count.
#3 – How to count the number of times a specific word appears in a row or a column?
The steps to count the number of times a particular word appears in a row or a column are listed as follows:
If the column is named “NamesColumn,” the cells in this column will use “NamesColumn” for reference.
Apply the formula “=COUNTIF(NamesColumn,”Jack”)” to count the number of times “Jack” appears in the “NamesColumn.”
Note: Every time a new name is added to a cell of “NamesColumn,” the result of the formula will automatically update.
The formula to count words of a particular range is “=LEN(TRIM(cell))-LEN(SUBSTITUTE(cell,” “,””))+1.”
The word count formula is combined with the SUM or SUMPRODUCT function to handle arrays.
The SUBSTITUTE function replaces all the spaces of the cell with a vacant content string (“).
The LEN function restores the length of the string without spaces.
The TRIM function removes the leading and trailing spaces found at the beginning or at the end of the text.
The number of words in a cell is equivalent to the number of spaces plus 1.
This has been a guide to Word Count in Excel. Here we discuss how to count the total number of words in a cell and a range of cells using Excel formulas (LEN, SUBSTITUTE, TRIM) along with practical examples and a downloadable Excel template. You may learn more about Excel from the following articles –
This short tutorial explains the basics of the Excel COUNT and COUNTA functions and shows a few examples of using a count formula in Excel. You will also learn how to use the COUNTIF and COUNTIFS functions to count cells that meet one or more criteria.
As everyone knows, Excel is all about storing and crunching numbers. However, apart from calculating values, you may also need to count cells with values – with any value, or with specific value types. For example, you may want a quick count of all items in a list, or the total of inventory numbers in a selected range.
Microsoft Excel provides a couple of special functions for counting cells: COUNT and COUNTA. Both all very straightforward and easy-to-use. So let’s take a quick look at these essential functions first, and then I will show you a few Excel formulas to count cells that meet certain condition(s), and clue you in on the quirks in counting some value types.
Excel COUNT function – count cells with numbers
You use the COUNT function in Excel to count the number of cells that contain numerical values.
The syntax of the Excel COUNT function is as follows:
COUNT(value1, [value2], …)
Where value1, value2, etc. are cell references or ranges within which you want to count cells with numbers.
In the modern versions of Excel 2016, Excel 2013, Excel 2010, and Excel 2007, the COUNT function accepts up to 255 arguments. In earlier Excel versions, you can supply up to 30 ‘values’.
For example, the following formula returns the total number of numeric cells in range A1:A100:
Note. In the internal Excel system, dates are stored as serial numbers and therefore the Excel COUNT function counts dates and times as well.
Using COUNT function in Excel – things to remember
Below are the two simple rules by which the Excel COUNT function works.
If an argument(s) of an Excel Count formula is a cell reference or range, only numbers, dates and times are counted. Blanks cells and cells containing anything but a numeric value are ignored.
If you type values directly into the Excel COUNT arguments, the following values are counted: numbers, dates, times, Boolean values of TRUE and FALSE, and text representation of numbers (i.e. a number enclosed in quotation marks like “5”).
For example, the following COUNT formula returns 4, because the following values are counted: 1, “2”, 1/1/2016, and TRUE.
=COUNT(1, "apples", "2", 1/1/2016, TRUE)
Excel COUNT formula examples
And here are a few more examples of using the COUNT function in Excel on different values.
To count cells with numeric values in one range, use a simple count formula like
The following screenshot demonstrates which types of data are counted and which are ignored:
To count several non-contiguous ranges, supply all of them to your Excel COUNT formula. For example, to count cells with numbers in columns B and D, you can use formula similar to this:
If you want to count numbers that meet certain criteria, use either the COUNTIF or COUNTIFS function.
Excel COUNTA function – count cells with values (non-blank cells)
The COUNTA function in Excel counts the number of cells in a range that are not empty.
The syntax of the Excel COUNTA function is akin to that of COUNT:
COUNTA(value1, [value2], …)
Where value1, value2, etc. are cell references or ranges where you want to count non-blank cells.
For example, to count cells with value in range A1:A100, use the following formula:
To count non-empty cells in several non-adjacent ranges, use a COUNTA formula similar to this:
=COUNTA(B2:B10, D2:D20, E2:F10)
As you can see, the ranges supplied to an Excel COUNTA formula do not necessarily need to be of the same size, i.e. each range may contain a different number of rows and columns.
Please keep in mind that Excel’s COUNTA function counts cells containing any type of data, including:
Dates / times
Boolean values of TRUE and FALSE
Error values like #VALUE or #N/A
Empty text strings (“”)
In some cases, you may be perplexed by the COUNTA function’s result because it differs from what you see with your own eyes. The point is that an Excel COUNTA formula may count cells that visually look empty, but technically they are not. For example, if you accidentally type a space in a cell, that cell will be counted. Or, if a cell contains some formula that returns an empty string, that cell will be counted as well.
In other words, the only cells that the COUNTA function does not count are absolutely empty cells.
The following screenshot demonstrates the difference between Excel COUNT and COUNTA functions:
Tip. If you just want a quick count of non-blank cells in a selected range, simply have a look at Status Bar at the bottom right corner of your Excel window:
If you just want a quick count of, simply have a look at Status Bar at the bottom right corner of your Excel window:
Excel COUNTIF function – count cells that meet one condition
The COUNTIF function is purposed for counting cells that meet a certain criterion. Its syntax requires 2 arguments, which are self-explanatory:
In the first argument, you define a range where you want to count cells. And in the second parameter, you specify a condition that should be met.
For example, to count how many cells in range A2:A15 are “Apples”, you use the following COUNTIF formula:
Instead if typing a criterion directly in the formula, you can input a cell reference as demonstrated in the following screenshot:
For more information about using the COUNTIF function in Excel, check out the following tutorial: COUNTIF in Excel – count if not blank, greater than, duplicate or unique
Excel COUNTIFS function – count cells that match several criteria
The COUNTIFS function is similar to COUNTIF, but it allows specifying multiple ranges and multiple criteria. Its syntax is as follows:
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
The COUNTIFS function was introduced in Excel 2007 and is available in all later versions of Excel 2010, 2013, and 2016.
For example, to count how many “Apples” (column A) have made $200 and more sales (column B), you use the following COUNTIFS formula:
And again, to make your COUNTIFS formula more versatile, you can supply cell references as the criteria:
You will find plenty more formula examples here: How to use Excel COUNTIFS function with multiple criteria.
Count the number of cells in a range (ROWS and COLUMNS functions)
If you need to find out the total number of cells in a rectangular range, utilize the ROWS and COLUMNS functions, which return the number of rows and columns in an array, respectively:
For example, to find out how many cells there are in a given range, say A1:D7, use the following formula:
You may also be interested in
This Excel tutorial explains how to use the Excel IF-THEN-ELSE statement (in VBA) with syntax and examples.
The Microsoft Excel IF-THEN-ELSE statement can only be used in VBA code. It executes one set of code if a specified condition evaluates to TRUE, or another set of code if it evaluates to FALSE.
The IF-THEN-ELSE statement is a built-in function in Excel that is categorized as a Logical Function. It can be used as a VBA function (VBA) in Excel. As a VBA function, you can use this function in macro code that is entered through the Microsoft Visual Basic Editor.
Please read our IF function (WS) page if you are looking for the worksheet version of the IF statement as it has a very different syntax.
The syntax for the IF-THEN-ELSE statement in Microsoft Excel is:If condition_1 Then result_1 ElseIf condition_2 Then result_2 ... ElseIf condition_n Then result_n Else result_else End If
Parameters or Argumentscondition_1, condition_2, … condition_n The conditions that are to be evaluated in the order listed. Once a condition is found to be true, the corresponding code will be executed. No further conditions will be evaluated. result_1, result_2, … result_n The code that is executed once a condition is found to be true. result_else The code that is executed when all previous conditions (condition1, condition2, … condition_n) are false.
The IF-THEN-ELSE statement evaluates the conditions in the order listed. It will execute the corresponding code when a condition is found to be true. If no condition is met, then the Else portion of the IF-THEN-ELSE statement will be executed.
Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
Example (as VBA Function)
The IF-THEN-ELSE statement can only be used in VBA code in Microsoft Excel.
Let’s look at some Excel IF-THEN-ELSE statement function examples and explore how to use the IF-THEN-ELSE statement in Excel VBA code:
First, let’s look at a simple example.If LRegion ="N" Then LRegionName = "North" End If
Next, let’s look at an example that uses ElseIf.If LRegion ="N" Then LRegionName = "North" ElseIf LRegion = "S" Then LRegionName = "South" ElseIf LRegion = "E" Then LRegionName = "East" ElseIf LRegion = "W" Then LRegionName = "West" End If
Finally, let’s look at an example that uses Else.If LRegion ="N" Then LRegionName = "North" ElseIf LRegion = "S" Then LRegionName = "South" ElseIf LRegion = "E" Then LRegionName = "East" Else LRegionName = "West" End If
Example#1 from Video
In the first video example, we are going to use the IF-THEN-ELSE statement to update cell C2 with “North”, “South”, “East” or “West” depending on the region code entered in cell A2.
So if we entered “N” in cell A2, we want “North” to appear in cell C2. If we entered “S” in cell A2, we want “South” to appear in cell C2, and so on.Sub totn_if_example1() Dim LRegion As String Dim LRegionName As String LRegion = Range("A2").Value If LRegion = "N" Then LRegionName = "North" ElseIf LRegion = "S" Then LRegionName = "South" ElseIf LRegion = "E" Then LRegionName = "East" Else LRegionName = "West" End If Range("C2").Value = LRegionName End Sub
Example#2 from VideoSub totn_if_example2() For Each grade In Range("B2:B8") If grade = "A" Or grade = "B" Then grade.Offset(0, 1).Value = "Great work" ElseIf grade = "C" Then grade.Offset(0, 1).Value = "Needs Improvement" Else grade.Offset(0, 1).Value = "Time for a Tutor" End If Next grade End Sub
Bạn đang xem bài viết How To Get The Word Count In Excel (Using Simple Formulas) trên website Trucbachconcert.com. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!