Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to calculate two-way lookup VLOOKUP in Excel Table
  • To count total rows in a range in Excel
  • Offset in Excel
  • Last row number in range
  • How to use Excel LOOKUP Function

Data Analysis Examples

  • Managing Conditional Formatting Rules in Excel
  • Everything about Charts in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • How to count table columns in Excel
  • What-If Analysis: Scenarios and Goal Seek in Excel

Data Validation Examples

  • Excel Data validation unique values only
  • Excel Data validation date in next 30 days
  • Excel Data validation exists in list
  • Data validation must not exist in list
  • Excel Data validation no punctuation

How to count specific words in a cell in Excel

by

If you need to count how many times a specific a word (or any substring) appears inside a cell, you can use a formula that uses SUBSTITUTE and LEN.

Formula

=(LEN(text)-LEN(SUBSTITUTE(text,word,"")))/LEN(word)

Explanation

In our example, we are using this formula:

=(LEN(B4)-LEN(SUBSTITUTE(B4,C4,"")))/LEN(C4)

How this formula works

B4 is the cell we’re counting words in, and C4 contains the substring (word or any substring) you are counting.

SUBSTITUTE removes the substring from the original text and LEN calculates the length of the text without the substring. This number is then subtracted from the length of the original text. The result is the number of characters that were removed by SUBSTITUTE.

Worked Example:   How to translate letters to numbers in Excel

Finally, the number of characters removed is divided by the length of the substring. So, if a substring is 5 characters long, and there are 10 characters missing after it’s been removed from the original text, we know the substring appeared twice in the original text.

Handling problems

Counting words in Excel is tricky because Excel doesn’t support regular expressions. As a result, it’s difficult to build matches that target the words you want to count exactly, while ignoring substrings and other partial matches (look for “fox ” to avoid “foxes”). Punctuation and case variations make this problem quite challenging.

Worked Example:   Get position of 2nd 3rd and more instance of character in Excel

One workaround is to use another formula in a helper column to “normalize text” as a first step. Then use the formula on this page to count words wrapped in space characters to get an accurate count (i.e. you can look for ” fox ” in the normalized text.

Note: this approach is only as good as the normalized text you are able to create, and you might need to adjust the normalizing formula many times to get the result you need.

Worked Example:   Highlight cells that end with in Excel

Handling case

SUBSTITUTE is a case-sensitive function, so it will match case when running a substitution. If you need to count both upper and lower case occurrences of a word or substring, use the UPPER function inside SUBSTITUTE to convert the text to uppercase before running the substitution:

=(LEN(B4)-LEN(SUBSTITUTE(UPPER(B4),UPPER(C4),"")))/LEN(C4)

Because this formula converts the substring and the text to uppercase before performing the substitution, it will work equally well with text in any case.

Post navigation

Previous Post:

How to get last line in cell in Excel

Next Post:

How to find nth occurrence of character in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • FIND, FINDB functions: Description, Usage, Syntax, Examples and Explanation
  • How to check if cell contains all of many things in Excel
  • SUBSTITUTE function vs REPLACE function in Excel
  • PHONETIC function: Description, Usage, Syntax, Examples and Explanation
  • Extract last name from full name — Manipulating NAMES in Excel
  • Convert text date dd/mm/yy to mm/dd/yy in Excel
  • Generate series of dates by weekends in Excel
  • Display Days until expiration date in Excel
  • Dynamic date list in Excel
  • Excel Date & Time Functions Example
  • RATE function: Description, Usage, Syntax, Examples and Explanation
  • CUMPRINC function: Description, Usage, Syntax, Examples and Explanation
  • DB function: Description, Usage, Syntax, Examples and Explanation
  • NOMINAL function: Description, Usage, Syntax, Examples and Explanation
  • Calculate interest rate for loan in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles