This article was co-authored by How.com.vn staff writer, Nicole Levine, MFA. Nicole Levine is a Technology Writer and Editor for How.com.vn. She has more than 20 years of experience creating technical documentation and leading support teams at major web hosting and software companies. Nicole also holds an MFA in Creative Writing from Portland State University and teaches composition, fiction-writing, and zine-making at various institutions.
This article has been viewed 4,382 times.
This How.com.vn teaches you how to count how to use Microsoft Excel to find out how many times a word appears in a range, as well as how to count the number of words in a single cell.
Steps
Counting Instances of a Word in a Range
Open Excel on your computer. It’s in the All Apps area of the Startmenu in Windows, or the Applications folder in macOS.
Open the workbook you want to use. Press Control+O, select the file you want to access, then click Open.
Click an empty cell. This is where you’ll enter the formula.
Type the formula for counting the number of times a word occurs in a range. The format is =SUMPRODUCT(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,"apple","")))/LEN("apple"). You’ll need to make the following modifications:- Replace both instances of “A2:A7” with the range you want to evaluate.
- Replace both instances of “apple” with the word you’re counting.
Press ↵ Enter or ⏎ Return. The number of times the word shows up in this range now appears in the cell.Advertisement
Counting Words in a Cell
Open Excel on your computer. It’s in the All Apps area of the Startmenu in Windows, or the Applications folder in macOS.
Open the workbook you want to use. Press Control+O, select the file you want to access, then click Open.
Click an empty cell. This is where you’ll enter the formula.
Type the formula for counting the number of words in a cell. The format is =IF(LEN(TRIM(cell))=0,0,LEN(TRIM(cell))-LEN(SUBSTITUTE(cell," ",""))+1). You’ll need to make the following modification:- Replace all three instances of “cell” with the cell number you’re counting.
- For example, if you’re counting the words in A2, replace “cell” with A2 in the formula.
Press ↵ Enter or ⏎ Return. The number of words in the selected cell now appears in place of the formula.Advertisement
Expert Q&A
About this article
Is this article up to date?
⚠️ Disclaimer:
Content from Wiki How English language website. Text is available under the Creative Commons Attribution-Share Alike License; additional terms may apply.
Wiki How does not encourage the violation of any laws, and cannot be responsible for any violations of such laws, should you link to this domain, or use, reproduce, or republish the information contained herein.
- - A few of these subjects are frequently censored by educational, governmental, corporate, parental and other filtering schemes.
- - Some articles may contain names, images, artworks or descriptions of events that some cultures restrict access to
- - Please note: Wiki How does not give you opinion about the law, or advice about medical. If you need specific advice (for example, medical, legal, financial or risk management), please seek a professional who is licensed or knowledgeable in that area.
- - Readers should not judge the importance of topics based on their coverage on Wiki How, nor think a topic is important just because it is the subject of a Wiki article.
