您知道如何计算 Word 和 Powerpoint 中的单词(count words in Word and Powerpoint)。您有没有想过如何在Microsoft Excel上计算字数?是否有计算单词的选项或是否有公式可以这样做?在本指南中,我们将帮助您了解如何使用公式轻松计算 Excel 工作表中的单词。让我们看看这样做的公式并理解它。
Excel中计算单词的函数
与Microsoft Word或Powerpoint一样,Excel没有计算字数的工具。但是有一种方法可以计算单词。虽然这是一个复杂的过程,并且一次只适用于一个细胞,但我们将通过它并使其适用于一组细胞。要理解计算单词的公式,我们需要先了解公式中的几个函数。它们是LEN()
、TRIM()
和SUBSTITUTE()
。
- 该
LEN()
函数接受一个单元格并给出单元格中出现的字符数。它为我们提供了单元格中的字符数以及字符之间包含的空格数。 - 该
TRIM()
函数给出了出现在单元格中的字符数,该单元格在单元格中文本的开头和结尾处切割了我们的空白。 - 该
SUBSTITUTE()
函数用我们选择的另一部分替换部分文本或数据。这样做的语法是SUBSTITUTE(Cell,"old_text","new_text")
. 请记住始终将文本放在公式中的引号中。
我们在公式中使用这三个函数来获取字数。
如何在Excel中计算单词
要在Excel(Excel)中计算单词,我们将使用我们学到的上述函数。我们一起使用它们来给我们一个数字,我们想知道哪个是字数。由于Excel没有计算字数的工具,我们需要更有创意地使用这些函数来计算字数。为此,我们将使用LEN()
函数计算单元格中的字符数,然后使用SUBSTITUTE()
不带空格的函数计算字符数。然后,我们将两者的差加上 1 来得到字数。
简单来说,我们计算一个单元格中的空格数并将其加 1 以获得正确的字数。让我们看看我们如何做到这一点。
(Count Words)计算开头(Beginning)或结尾没有空格(Spaces)的单词
要计算单元格中文本开头或结尾没有空格的单词,我们只使用LEN()
和SUBSTITUTE()
函数。
那么,这样做的公式是,=LEN(cell)-LEN(SUBSTITUTE(Cell," ",""))+1
。
简单来说,我们计算单元格中的字符数,减去不带空格的单元格中的字符数,再加上 1 得到字数。
要计算Excel(Excel)工作表中的单词数,您必须计算每个单元格并将它们加在一起以获得整个工作表的最终计数。
阅读(Read):如何在 Microsoft Excel 中使用 DGET 函数(How to use the DGET function in Microsoft Excel)。
计算(Count)开头(Beginning)和结尾(End)有空格的单词
为了计算单元格中开头和结尾有空格的单词,我们TRIM()
在公式中包含了该函数。
那么,这样做的公式是,=LEN(TRIM(Cell))-LEN(SUBSTITUTE(Cell," ",""))+1
。
要获得整张纸的字数,您必须对每个单元格应用相同的公式并将所有字数相加以获得最终数字。
这就是我们在Excel(Excel)工作表中获取字数的方式。我们需要更加警惕、谨慎和创造性地获得数字,而不会出现任何错误或误算。在得出最终数字之前检查公式和函数。(Check)
阅读(Read):10 个最适合初学者的 Excel 提示和技巧(10 most useful Excel Tips and Tricks for beginners)
How to count words in Excel
Υоu knоw how to count words in Word and Powerpoint. Have you ever wondered how you can count words on Microsoft Excel? Is there an option to count words or is there a formula to do so? In this guide, we will help you understand how to count words in an excel sheet with formulas easily. Let’s see what the formula to do so and understand it.
Functions to count words in Excel
Like Microsoft Word or Powerpoint, Excel doesn’t have a tool to count words. But there is a way to count the words. Though it is a complex process and works for only one cell at a time, we will get through it and make it work for a group of cells. To understand the formula to count words, we need to understand a few functions in the formula first. They are LEN()
, TRIM()
, and SUBSTITUTE()
.
- The
LEN()
function takes a cell and gives the number of characters that appear in the cell. It gives us the number of characters in a cell with the number of spaces included between the characters. - The
TRIM()
function gives the number of characters that appear in a cell cutting our white spaces at the beginning and the end of the text in the cell. - The
SUBSTITUTE()
function replaces a part of text or data with another piece of our choice. The syntax to do so is SUBSTITUTE(Cell,"old_text","new_text")
. Remember to put text in quotes in the formula always.
We use these three functions in a formula to get the word count.
How to count words in Excel
To count words in Excel, we will use the above functions that we learned. We use them together to give us a number that we want to know which is the word count. As Excel does not have a tool to count the number of words, we need to be more creative in using the functions to get the word count. To do so, we will count the number of characters in the cell using LEN()
function and then count the number of characters using the SUBSTITUTE()
function without spaces. Then, we add the difference between the two with 1 to get the word count.
In simple words, we count the number of spaces in a cell and add it with 1 to get the correct word count. Let’s see how we can do it.
Count Words without Spaces at the Beginning or End
To count the words without spaces at the beginning or at the end of the text in a cell, we use only LEN()
and SUBSTITUTE()
functions.
Then, the formula to do so would be, =LEN(cell)-LEN(SUBSTITUTE(Cell," ",""))+1
.
To understand it in simple terms, we are counting the number of characters in the cell and subtracting it with the number of characters in the cell without spaces, and adding it with 1 to get the word count.
To count the number of words in an Excel sheet, you have to count each cell and add them together to get a final count of the whole sheet.
Read: How to use the DGET function in Microsoft Excel.
Count words with Spaces at the Beginning and End
To count words with spaces at the beginning and end in a cell, we include the TRIM()
function in the formula.
Then, the formula to do so would be, =LEN(TRIM(Cell))-LEN(SUBSTITUTE(Cell," ",""))+1
.
To get the word count of the whole sheet, you have to apply the same formula to every cell and add all the counts to get a final number.
This is how we get the word count in an Excel sheet. We need to be more vigilant, careful, and creative to get the number without any errors or miscalculations. Check formulas and functions before concluding on a final number.
Read: 10 most useful Excel Tips and Tricks for beginners